Add character in each cell (MS Excel)

You have MSExcel sheet with 100 cells filled with 001-100. And you need to add "A" in front all of them. You don't need to work whole day on that. All you have to do is make one Macro in VB.

Option Explicit

Sub Add_A()
Dim Last, Z As Variant, X As Variant
{here add title of your sheet}
Last = ActiveCell.SpecialCells(xlLastCell).Address
ActiveSheet.Range(Cells(1, 1), Last).Select
Z = Selection.Address
{taking adress}
For Each X In ActiveSheet.Range(Z)
{working while...}
If Len(X) > 0 Then
{finding cell with content}
X.FormulaR1C1 = Chr(65) & X.Text
{65 is code for 'A'}
X.FormulaR1C1 = ""
{change nothing if cell is empty }
End If
End Sub

Save this like new Macro and run it. Macro "Add_A" will add "A" in front of cells content.

Subscribe in a reader


At 2:26 PM, Anonymous Anonymous said...

The example used is not a practical one. You would use this method if you had cells with different information in each. Otherwise it would be much quicker to use the find/replace feature excel has. This runs much quicker.

At 3:07 PM, Anonymous Anonymous said...

It's too bad the comment statements are not indicated by a leading apostrophe.

At 9:34 AM, Anonymous Anonymous said...

This example i have changed and it works fine. I have question how to add 0 in front of a number that will not be erased by excel.

At 6:45 AM, Blogger Shep said...

I think using the Concatenate function followed by Paste Values would be much simpler!


Post a Comment

Links to this post:

Create a Link

<< Home