EXCEL VBA：创建一个简单的循环来为每一行重复一个过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17427662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXCEL VBA : Creating a simple loop to repeat a process for each row
提问by YannickHelmut
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!
我知道这似乎是一个非常简单的问题，但我尝试了不同的方法来创建一个循环来执行我正在寻找的操作：基本上我有一个包含 4 列（未知行数）的 excel 表，我想在其中输入数据。然后将此数据镜像到包含我用来创建多个 PDF 文件的“印刷设计”的第二张纸。问题是：我现在尝试了 4 天来创建一个循环，但没有取得任何成果！
If you could help me, this is the data entry: SCREENSHOT
如果你能帮助我，这是数据条目： 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.
我实际上想为每一行创建一个 SINGLE PDF 文件，因此对第 4、5、6 行等执行此操作，直到 VBA 找到一个空行。
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!
任何帮助将不胜感激，在此先感谢我在 Stackoverflow 上找到的所有帮助，希望能有所帮助！
Thanks,
谢谢，
Yannick
雅尼克
回答by Floris
In general, a good way to create a loop in VBA involves these steps:
通常，在 VBA 中创建循环的好方法包括以下步骤：
- 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 myRange as Range） - 使用如下循环遍历范围的（单元格、行）：
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 myRangeto 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 turnRowIntoPdfwith 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.
这将定义myRange为从单元格 (4,4) 开始的范围 - 即 D4 - 直到有条目为止。然后它将依次循环这些单元格中的每一个（D4、D5、D6、...）并调用turnRowIntoPdf带有参数的 Sub r（依次是这些单元格中的每一个）。然后，您可以编写一个以该参数作为输入的子程序，并创建 pdf。
Think you can manage it from there?
认为你可以从那里管理它？
