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

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

--
Message posted from http://www.ExcelForum.com

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


"mudraker " wrote in message
...
Bob


Try these for starts

Both macros replace the existing number in column B with one that has
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 Sub


---
Message posted from http://www.ExcelForum.com/

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

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


"mudraker " wrote in message
...
Bob


Try these for starts

Both macros replace the existing number in column B with one that has
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 Sub


---
Message posted from http://www.ExcelForum.com/

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

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

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


should be "ACTIVE worksheet"


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

Bob

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

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