Adding a "1", please help!

A

alexm999

I'd like to add a "1" (number one) in front of everything in column A. I
can use =1&A1 but it's too tedius to do to every record in every
spreadsheet.

I tried creating a macro, but for blank rows say A1 to A100 have
numbers, but after that it adds a 1 in an empty cell.

Is there anyone who can help me with this?
 
J

Jay

I'd like to add a "1" (number one) in front of everything in column A. I
can use =1&A1 but it's too tedius to do to every record in every
spreadsheet.

I tried creating a macro, but for blank rows say A1 to A100 have
numbers, but after that it adds a 1 in an empty cell.
Click to expand...

Instead of =1&A1, this might work:
=IF(A1="","",1&A1)
 
P

PCLIVE

Try something like this:

Sub Test()
For Each cell In Range("A1:A100")
cell.Value = 1 & cell.Value
Next cell
End Sub

Regards,
Paul
 
P

PCLIVE

Ok,

Sorry for the multiple posts. That last one still puts the one in blanks.
This of cours won't be the only way, and probably not the best way. But you
should be able to get what you want with this.

Sub Test()
For Each cell In Range("A1:A100")
If cell.Value = "" Then GoTo skip
cell.Value = 1 & cell.Value
skip:
Next cell
End Sub


If you want the entire A column, just change that line:
For Each cell In Range("A:A")
 
J

JMay

PCLIVE:
When I paste in your macro this line shows up IN RED;
If cell.Value = "" Then GoTo skip
and, of course when I run the macro
I get a "compile error" - syntax///
What's happening??????
TIA,,
 
Top