macros help...

[parnless]

I have a vertical list of 4 digit numbers that I want to add a zero(0) to the
beginning of the number. For example a number might be 3324 and I want to
add a 0 to the left of the 4 digits to end up looing like 03324. The problem
is this: This vertical list of numbers is almost 1200 lines deep. Some of
the numbers in this list are already 5 digits, whcih is what I want. I need
every number on each line to be 5 digits long. I did create a macro to add
the 0 to the 4 digit number, then drop down to the next line, but when I run
the macro again for the next line, it changes the number on the next line to
the same number on the line above. I want to be able to start in a cell, hit
Ctrl G and have it add a 0 to the 4 digit number in that cell, then drop down
a line and allow me to hit Ctrl G to do the same thing to the 4 digit number
in that particular cell.

The left column in the spreadsheet looks like this:
2345
2365
2476
2490
And when I get through I want it to look like this:
02345
02365
02476
02490

What it is doing now is this:
02345
02345
02345
02345

It's changing each number in each row to look exactly like the number in the
first row. I would appreciate any help anyone can offer. I used to use
Lotus 123 and did not have this kind of problems. Thanks in advance for any
advise or solutions.

 

[FSt1]

hi
me and lotus go back. before windows. before the mouse. i was writing macros
in lotus before windows. before excel. i understand your delema. but.... the
world has gone excel crazy. now a days, everyone thinks that excel is so much
better than lotus. ha....if they only knew. sigh. there is nothing in excel
that i haven't already done in lotus and the only thing i can tell is that
the menus are different .
i still have lotus and still use it.
but back to reality and the real world (as defined by Bill) . post your
code. me or someone might be able to edit it to your requirments.

regards
FSt1

 

[Rick Rothstein \(MVP - VB\)]

Is this just for display or will you have to make use of the numbers with
their leading zeroes in formulas in other cells? If simply for display (that
is, they will **not** be used anywhere else), you can Custom Format the
cells using 00000 as the pattern (select the column with your numbers in
them, right click and select Format Cells, click the Number tab, select
Custom from the Category list and type in 00000 in the Type field).

But, if you must physically change the values so they can be used elsewhere,
then the macro below will do that (by the way, you will not have to
repeatedly press Ctrl+G... the macro will do the whole column for you
automatically). Go to the worksheet with your numbers on it and right click
the tab at the bottom of this sheet and then select View Code from the menu
that pops up. Now, copy/paste the code below into the code window that
appeared. After you do that, you will have to replace my guesses at the
information you didn't tell us about with your actual information. In the
Const statement, change my guess at Column A to the letter designation for
the column that has your numbers in them. Next, replace the "Sheet3" in the
With statement to the actual sheet name where your numbers are located.
Okay, that's it. Go back to your worksheet and press Alt+F8; select
AddLeadingZeroes from the list and click the RUN button. All your numbers in
the designated column on the designated sheet will change to 5-character
text with leading zeroes (numbers can't hold leading zeroes, so the values
must be converted to text to accomplish what you want).

Sub AddLeadingZeroes()
Dim X As Long
Dim LastRow As Long
Const ColumnLetter As String = "A"
With Worksheets("Sheet3")
LastRow = .Cells(Rows.Count, ColumnLetter).End(xlUp).Row
For X = 1 To LastRow
.Cells(X, ColumnLetter).Value = "'" & Right("00000" & .Cells(X,
ColumnLetter).Value, 5)
Next
End With
End Sub

Rick

 

[parnless]

I copied the macro as you requested. I clicked on the cell with the number
6150 in it, hit Ctrl t, and the macro changed the number to 06150 and moved
one cell down. Sound good huh? I then hit Ctrl t again and the macro
changed the number in that cell to 06150. It just copied what I did in the
cell above. I need it to add a 0 in front of the number that is in the cell,
not keep putting the same number in it. Anyway, here is the macro named
Test1:

Sub Test1()
'
' Test1 Macro
' Macro recorded 3/13/2008
'
' Keyboard Shortcut: Ctrl+t
'
ActiveCell.FormulaR1C1 = "'06150"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("B16").Select
End Sub

 

[Don Guillett]

Can't you just apply a custom format of 00000