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
Sheets("Sheet1").Select
{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'}
Else
X.FormulaR1C1 = ""
{change nothing if cell is empty }
End If
Next
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
4 Comments:
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.
It's too bad the comment statements are not indicated by a leading apostrophe.
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.
I think using the Concatenate function followed by Paste Values would be much simpler!
Post a Comment
<< Home