adding zeros in front of numbers

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
Hey All,

Have a macros here that adds a new row and populates the first column with a code...

ActiveSheet.Unprotect
Rows(Range("A65536").End(xlUp).row).Copy Range("A65536").End(xlUp).Offset(1, 0)
Range("a65536").End(xlUp).Value = Range("F2").Value & "-" & Range("F3").Value & "-DS-" & Range("a65536").End(xlUp).row - 9
Range("e65536").End(xlUp).Value = ""
myrow = Range("A65536").End(xlUp).row - 1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

The code produced looks something like '143232-133-DS-1' , where the '143232' and '133' come from cells F2 and F3. The incremental number comes from counting the rows in the sheet and where the data starts. Thing is, the first 99 codes need to have 3 digits at the end. So instead of '*-DS-1' it should be '*-DS-001' or '*-DS-026', etc.

How do I modify the code above to do that?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Range("a65536").End(xlUp).Value = Range("F2").Value & "-" & Range("F3").Value & "-DS-" & Range("a65536").End(xlUp).row - 9

becomes

Range("a65536").End(xlUp).Value = Range("F2").Value & "-" & Range("F3").Value & "-DS-" & format(Range("a65536").End(xlUp).row - 9,"000")

I won't get into how ugly this is as far as expensively doing the same thing (end(xlup)) six times.

{Edit}Removed erroneous parenthesis from formula.{/edit}
 

gaynard_nelson

Active Member
Joined
Dec 4, 2002
Messages
323
ammdumas said:
Hey All,

Have a macros here that adds a new row and populates the first column with a code...

ActiveSheet.Unprotect
Rows(Range("A65536").End(xlUp).row).Copy Range("A65536").End(xlUp).Offset(1, 0)
Range("a65536").End(xlUp).Value = Range("F2").Value & "-" & Range("F3").Value & "-DS-" & Range("a65536").End(xlUp).row - 9
Range("e65536").End(xlUp).Value = ""
myrow = Range("A65536").End(xlUp).row - 1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

The code produced looks something like '143232-133-DS-1' , where the '143232' and '133' come from cells F2 and F3. The incremental number comes from counting the rows in the sheet and where the data starts. Thing is, the first 99 codes need to have 3 digits at the end. So instead of '*-DS-1' it should be '*-DS-001' or '*-DS-026', etc.

How do I modify the code above to do that?

If You were entering a number in a cell you would need to setup a Custom number format for the maximum number of 0's you will need as the number itself grows the actual number will replace the leading zeros in the code. The format is 00#

In your case since you are concantenating a string from other cells you would have to build into your formula that if you have 1 digit you add "00" in the cell if two digits you add "0" in the cell.
 

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
Hmm. Code doesn't like the comma before the "000"). I'm brain farting here... :unsure:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Not you, me. An extra parenthesis somehow snuck in there on me. Edited. Try now, please.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,112
Messages
5,570,259
Members
412,314
Latest member
yazanwael
Top