Insert text in a cell with a macro
 

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

--
HTH

Bob Phillips

"kayabob" wrote in message
...
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.

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!

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"kayabob" wrote in message
...
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.

For a non-Macro (?micro?) formula use:

=CHAR(39)&"0"&A1
--
Gary's Student


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"kayabob" wrote in message
...
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.

I have the same issue - did you ever figure it out? If so, I would love to
know how you did it!

"kayabob" wrote:

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.

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.

--
HTH

Bob Phillips

"kayabob" wrote in message
...
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!

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

"kayabob" wrote in message
...
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.