Insert text in a cell with a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a series of numbers (SSN) that I need to turn into text, so I want a
macro that will put an apostrophe and a zero at the beginning of the numbers.
I know that there are other ways to change a number to text (formatting,
find/replace to add the apostrophe) but for what we are doing, manually
editing to add the apostrophe and zero is the only thing that works. SO.. to
my question. When I make the macro to do this, of course it simply adds the
apostrophe and zero and repeats the number from the first cell over and over
again. How can I make it select each cell's new data, move to the beginning
of the number and add the apostrophe? Here is my macro that is obviously not
working.

ActiveCell.FormulaR1C1 = "'012365564"
ActiveCell.Offset(1, 0).Range("A1").Select

I see that the problem is that it is picking up the literal from the first
line, but how can I make the macro edit the new numbers in each line and not
repeat this text over and over.
 
iLatRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
Cells(i,"A").Value = "'0" & C ells(i,"A").VAlue
Next i
 
Excellent! Thank you. It errored on first try, but I changed to LatRow to
LastRow and C ells to remove the space and it worked perfectly. I bet your
fingers were flying so that you could give me a quick answer!
 
I have the same issue - did you ever figure it out? If so, I would love to
know how you did it!
 
No, it is because there are so many questions of this nature that I just
air-code them, and spell-checker doesn't catch those :-). I should know
better.
 
Back
Top