Problem in Excel Macro Code

[Ejaz]

I have a text data in Column A1..A10000, ie 3214437255
I want to add "0" in all Rows of Column A, ie 03214437255

for this problem I want a macro code, which add "0" to all rows automactically

 

[repairman615]

Hello,

Alt + F11 open VBEditor
Keystrokes ALT - I - M to insert module

paste:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddAZero()<br><br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A1:A10000")<br>        cell.Value = "0" & cell.Text<br>    <SPAN style="color:#00007F">Next</SPAN> cell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

close the editor

ALT + F8
run add a zero

It may take a min.

-Jeff

 

[Sektor]

I want to add to repairman's solution: cells' format must be Text.

 

[Ejaz]

thanks for prompt reply,

this code must stop automtically, where Column A Blank or empty, can we use if endif, pl correct existing code

 

[Ejaz]

pl provide your solution code

 

[VoG]

Try

Sub AddAZero()

Dim cell As Range
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each cell In Range("A1:A" & LR)
        cell.NumberFormat = "@"
        cell.Value = "0" & cell.Text
    Next cell

End Sub

 

[Ejaz]

thanks, code work, it should stop automaticall where Column A empty

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] AddAZero()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]

    i = 1
    [COLOR="Blue"]While[/COLOR] [COLOR="Blue"]Not[/COLOR] IsEmpty(Cells(i, 1))
        [COLOR="Blue"]With[/COLOR] Cells(i, 1)
            .NumberFormat = "@"
            .Value = "0" & .Text
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
        i = i + 1
    [COLOR="Blue"]Wend[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[VoG]

it should stop automaticall where Column A empty

It does, doesn't it?

 

[Sektor]

VoG, if there's gap, it won't stop 'cause you define last row from bottom to top.