Need help to write macro please

B

Bob

I am trying to write a macro that will format column B for text and then
insert four zero's (0000) infront of an existing number in the cell starting
at line 2 and continuing for 100 lines into that column of a spreadsheet.
The cells in the column are already populated with 8 digit numbers.
I'm a beginner at this and have been able to insert the zero's but it takes
the number from the cell that I did the macro on and repopulates this number
into other sheets. ( Am I making any sense?)
Would anyone have any ideas of guidance?
Many thanks
Bob
 
M

mudraker

Bob


Try these for starts

Both macros replace the existing number in column B with one that ha
0000 at the start


Macro1 runs over b2 to b100

Macro2 runs over b2 to last used cell in column B


Sub Macro1()
Dim Rng As Range
Columns("B:B").NumberFormat = "@"
For Each Rng In Range("b2:b100")
If Rng.Value <> "" Then
Rng.Value = "0000" & Rng.Value
End If
Next Rng
End Sub



Sub Macro2()
Dim Rng As Range
Columns("B:B").NumberFormat = "@"
For Each Rng In Range("b2:b" _
& Range("b" & Rows.Count).End(xlUp).Row)
If Rng.Value <> "" Then
Rng.Value = "0000" & Rng.Value
End If
Next Rng
End Su
 
B

Bob Kilmer

Bob,

Workbooks("Book1.xls").Worksheets("Sheet1").Range("B2:B102").Value = _
"'0000" & Workbooks("Book1.xls").Worksheets("Sheet1").Range("B2:B102").Text

If you are satisfied always to act on the worksheet, you can use.

Range("B2:B102").Value = "'0000" & Range("B2:B102").Text

Bob
 
B

Bob

Mudraker
Thanks for the reply.
Tried copying and pasting into (replacing) existing macro and keep getting
problems
This is exactly what I've pasted in

Sub zeros()
'
' zeros Macro
' Macro recorded 5/26/2004 by Bob
'
' Keyboard Shortcut: Ctrl+b
'
Sub Macro2()
Dim Rng As Range
Columns("B:B").NumberFormat = "@"
For Each Rng In Range("b2:b" _
& Range("b" & Rows.Count).End(xlUp).Row)
If Rng.Value <> "" Then
Rng.Value = "0000" & Rng.Value
End If
Next Rng
End Sub

It keeps coming back "compile error - expected end sub"
Any suggestions -
Thanks very much
 
B

Bob

Mudraker - I figured it out and it works GREAT! Thank you
Thank you very much to all.
Very much appreciated.
Best Rgds
Bob
 
B

Bob Kilmer

Bob Kilmer said:
Bob,

Workbooks("Book1.xls").Worksheets("Sheet1").Range("B2:B102").Value = _
"'0000" & Workbooks("Book1.xls").Worksheets("Sheet1").Range("B2:B102").Text

If you are satisfied always to act on the worksheet, you can use.
Click to expand...


should be "ACTIVE worksheet"
 
B

Bob Kilmer

Your do not have matching Sub-End Subs. You have one too many Subs. Remove
the line "Sub Macro2()".

Bob said:
Mudraker
Thanks for the reply.
Tried copying and pasting into (replacing) existing macro and keep getting
problems
This is exactly what I've pasted in

Sub zeros()
'
' zeros Macro
' Macro recorded 5/26/2004 by Bob
'
' Keyboard Shortcut: Ctrl+b
'
Sub Macro2()
Dim Rng As Range
Columns("B:B").NumberFormat = "@"
For Each Rng In Range("b2:b" _
& Range("b" & Rows.Count).End(xlUp).Row)
If Rng.Value <> "" Then
Rng.Value = "0000" & Rng.Value
End If
Next Rng
End Sub

It keeps coming back "compile error - expected end sub"
Any suggestions -
Thanks very much
Click to expand...
 
Top