EXCEL VBA : Creating a simple loop to repeat a process for each row

Question

I know it might seem to be a very simple question but I tried different methods to create a loop that would do what I'm looking for: Basically I have an excel sheet with 4 columns (unknown number of rows) in which I want to enter data. This data is then mirrored to a second sheet that contains the "printing design" that I use to create multiple PDF files. Problem is: I tried for 4 days now to create a loop and have not achieved anything!

If you could help me, this is the data entry: SCREENSHOT

Public Sub InputData()

Dim strCap As String
strCap = Sheets("INPUT").Cells(4, 3).Value
Label1.Caption = strCap

Dim strCap2 As String
strCap2 = Sheets("INPUT").Cells(4, 5).Value
Label2.Caption = strCap2

If Sheets("INPUT").Cells(4, 4) = "OE" Then
    Image1.Picture = LoadPicture(ActiveWorkbook.Path & "\OE_Logo.jpg")
Else
    Image1.Picture = LoadPicture(ActiveWorkbook.Path & "\SF_Logo.jpg")
End If

If Sheets("INPUT").Cells(4, 6) = "OE" Then
    Image2.Picture = LoadPicture(ActiveWorkbook.Path & "\OE_Logo.jpg")
Else
    Image2.Picture = LoadPicture(ActiveWorkbook.Path & "\SF_Logo.jpg")
End If

Application.Calculate

Call PrintPDF

End Sub


Sub PrintPDF()
Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
 '/// Change the output file name here! ///
sPDFName = "Affidavit" & " " & Sheets("INPUT").Cells(4, 3) & "_" & Sheets    ("INPUT").Cells(4, 5) & ".pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
 'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
With pdfjob
    If .cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
        vbOKOnly, "PrtPDFCreator"
        Exit Sub
    End If
    .cOption("UseAutosave") = 1
    .cOption("UseAutosaveDirectory") = 1
    .cOption("AutosaveDirectory") = sPDFPath
    .cOption("AutosaveFilename") = sPDFName
    .cOption("AutosaveFormat") = 0 ' 0 = PDF
    .cClearCache
End With
 'Print the document to PDF

Sheets("AFFIDAVIT CREATOR").PrintOut Copies:=1, From:=1, To:=1, ActivePrinter:="PDFCreator"
 'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
    DoEvents
Loop
pdfjob.cPrinterStop = False
 'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
    DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub

I actually want to create One SINGLE PDF file for each row, so do this for row 4, 5, 6 etc. till VBA finds an empty row.

Any help would be highly appreciated, thanks in advance for all the help I was able to find on Stackoverflow and hopefully help to come!

Thanks,

Yannick

chuff · Accepted Answer · 2013-07-02 14:19:04Z

2

In general, a good way to create a loop in VBA involves these steps:

Define the range of cells over which you want to loop
Assign the range to a variable (declared with Dim myRange as Range)
Loop over the (cells, rows) of the range with a loop like this:

    Dim r as Range, myRange as Range
    Set myRange = Range(Sheet("INPUT").cells(4,4), Sheet("INPUT").cells(4,4).end(xlDown))
    For Each r in myRange.Cells
      turnRowIntoPdf r
    Next

This will define myRange to be the range that starts at cell (4,4) - i.e. D4 - and goes as far down as there are entries. It will then loop over each of these cells in turn (D4, D5, D6, ...) and call a Sub turnRowIntoPdf with parameter r (which will be each of these cells in turn). You can then write a sub that takes this parameter as input, and creates the pdf.

Think you can manage it from there?

edited Jul 2, 2013 at 14:19

chuff

5,9061 gold badge24 silver badges26 bronze badges

answered Jul 2, 2013 at 14:12

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

YannickHelmut Over a year ago

A thousand times THANK YOU, you really helped me on this one.µ My only NEW PROBLEM is that I created a loop that is working but it generates identical PDF files, it basically prints out the same thing with different names but refreshes the page only at the end. Result: the code is not working like I want it to :(

Floris Over a year ago

May I suggest that you post your new problem as a new question with the relevant code - cant do that in a comment. "Here is my code, this is the result. I expected Thais other result What am I doing wrong?" Post a link to the new question here and I'll take a look.

YannickHelmut Over a year ago

Thanks! Already created a few hours ago. I got a suggestion that works more or less but slows down the macro a lot. I'm on an old company PC and when I'm launching the macro excel takes some time to create lets say 50 files. If I change the Print part to .PrintOut it actually doesnt work at all and Excel crashes :( NEW QUESTION IS HERE

Collectives™ on Stack Overflow

EXCEL VBA : Creating a simple loop to repeat a process for each row

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related