adding zeros in front of numbers

[ammdumas]

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?

 

[Greg Truby]

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]

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]

Hmm. Code doesn't like the comma before the "000"). I'm brain farting here...

 

[Greg Truby]

Not you, me. An extra parenthesis somehow snuck in there on me. Edited. Try now, please.

 

[ammdumas]

Got it. Gotta love syntax in coding.

Thx.