Excel to print unique reference numbers

**Vibu212** · 05-16-2011, 11:47 AM

Hello,

How are you?

I have a small question that I could not find an immediate answer through Google

I have a form which we want to send out to our vendors. To track the returned/completed forms coming back to us, we want to print the vendor number on each form.

The form that is to be sent out is same for all vendors. All vendor numbers (about 1,000) are available on a separate column in a sheet.

What should I do such that when I print the form (say 1,000 times), each vendor number is printed once according to the vendor table.

Thank you in advance for your time!

Regards,
Vibu

**TMShucks** · 05-16-2011, 11:59 AM

You would, most likely, use Excel VBA to loop through the vendor list and print one form for each vendor, inserting the Vendor ID and/or name on the form.

Post a sample workbook and vendor list for more detail.

Regards

**Vibu212** · 05-16-2011, 01:40 PM

Thank you. I've attached a sample. This sample is something I found over internet. Coding is also found on internet and I've modified it to somewhere

Option Explicit

Sub CommandButton1_Click()
Dim lSlip As String


lSlip = 0
Do Until lSlip = Range("i5").Value
With Sheet2

.Range("c6") = Range("i9").End(xlDown).Row
'Range("i9").Value + lSlip

.PrintPreview ' <-use for checking
'.PrintOut     ' <- use for printing
lSlip = lSlip + 1
End With
Loop
End Sub

Now I want to go from 0 to say 1,000th vendor. I've copied some vendor numbers on the side for testing purposes. I want these vendor numbers to be appearing on i9 one by one (moving downward) up till the loop is done (OR an empty cell is found)

The line I've highlighted (this line > .Range("c6") = Range("i9").End(xlDown).Row) is the one the one that requires modification (I assume?)

Thanks!

**TMShucks** · 05-16-2011, 02:05 PM

Try this:

Sub CommandButton1_Click()

Dim lStartRow As Long
Dim lEndRow As Long
Dim i As Long
Dim FormSh As Worksheet: Set FormSh = Sheets("Deposit Slip")

With FormSh
    lStartRow = .Range("I9").Row
    lEndRow = .Range("I" & .Rows.Count).End(xlUp).Row

    ' If Cell I6 = "Test" then use test values in I2 and I5
    If LCase(.Range("I1").Value) = LCase("Test") Then
        lStartRow = lStartRow + .Range("I2") - 1
        lEndRow = lStartRow + .Range("I5") - 1
    End If
End With

With FormSh
    For i = lStartRow To lEndRow
        .Range("c6") = .Range("i" & i).Value
        .PrintPreview ' <-use for checking
        '.PrintOut     ' <- use for printing
    Next 'i
End With
End Sub

Regards