Padding Zero's while printing and changing values

0
votes

I'm new here but hope you all can help with a solution I'm working towards. I'm working on an excel document and setting up a macro. It works until I try to add some logic to pad a number with zero's.

I'm trying to pad zero's in a select cell where the labels are less than 10, then add my integer. If the labels are greater than 9, I want to pad one less zero, likewise when they are greater than 99, one less from those with 10 or more.

My program asks the user how many labels they wish to print (1-999).

I've tried to add an IF statement within my For I = 1 To LabelCount:

    For I = 1 To LabelCount
      If I < 10 Then
      ActiveSheet.Range("C20").Value = "C906BGM0880000" & I
      ActiveSheet.PrintPreview
      Else
      ActiveSheet.Range("C20").Value = "C906BGM088000T" & I
      ActiveSheet.PrintPreview
      End If
    Next

The above did not work.

Sub IncrementPrint()
'updateby Tyler Garretson
    Dim LabelCount As Variant
    Dim xScreen As Boolean
    Dim I As Long
    On Error Resume Next
LInput:
    LabelCount = Application.InputBox("Please enter the number of copies you want to print:")
    If TypeName(LabelCount) = "Boolean" Then Exit Sub
    If (ActiveSheet.Range("F11").Value = "") Or (ActiveSheet.Range("F14").Value = "") Or (ActiveSheet.Range("C18").Value = "") Then
        MsgBox "Error Occurred.  Please enter values for Route, Stop, and Destination Name", vbExclamation
    ElseIf (LabelCount = "") Or (Not IsNumeric(LabelCount)) Or (LabelCount < 1) Or (LabelCount > 999) Then
        MsgBox "Error Occurred. Please enter 1 - 999", vbExclamation
    ElseIf LabelCount < 10 Then
        xScreen = Application.ScreenUpdating
        Application.ScreenUpdating = False
        For I = 1 To LabelCount
          ActiveSheet.Range("C20").Value = "C906BGM0880000" & I
          ActiveSheet.PrintPreview
        Next

        ActiveSheet.Range("C20").ClearContents
        Application.ScreenUpdating = xScreen
    End If
End Sub

User enters 11 labels that he or she wishes to print, the program prints out the following:

Label1: ABC00001
Label2: ABC00002
Label3: ABC00003
Label4: ABC00004
Label5: ABC00005
Label6: ABC00006
Label7: ABC00007
Label8: ABC00008
Label9: ABC00009
Label10: ABC00010
Label11: ABC00011
2
excelvba

2 Answers

2
votes

You want the Format command - Format(1, "00000") = 00001

Format(123,"00000") = 00123

0
votes

' This might be the basis of what you need

for a = 1 to 1000
   b = right("0000000000" & a,8)  ' B will always be 8 long and paaded left with 0's
next a

This works well with a text prefix too

for a = 1 to 1000
   c = "XYZ" & right("0000000000" & a,8)
next a