如何同时使用多个循环?

Question

下面的代码用于运行一个宏，该宏接受多个变量并循环遍历范围以将它们填充到另一个工作表中，然后保存为pdf。每次循环使用1，效果很好，但是当我尝试使用2时;它将运行第一个循环一次，然后只循环第二个循环。

有什么办法解决这个问题吗?

Sub CommandButton2_Click()
For Each c In Sheets("Resultaten").Range("N2:N1000").Cells '//Variable 1 range
If c = "" Then Exit For '//To end the loop when cells are empty
Sheets("Invuldocument").Range("B5").Value = c.Value '//Variable 1 transfer
For Each w In Sheets("Resultaten").Range("W2:W1000").Cells '//Variable 2 range
If w = "" Then Exit For '//To end the loop when cells are empty
Sheets("Invuldocument").Range("J5").Value = w.Value '//Variable 2 transfer
Application.Wait Now + #12:00:01 AM# ' //timer if the pc cannot handle the speed
Dim FileName As String '//Code below is to save as PDF in every loop
Dim Path As String
Application.DisplayAlerts = False
Path = "C:Users802435Desktoptest12"
FileName = "PRO-" & Range("B5").Value & "-" & Range("D41").Value & ".pdf"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True
Next '//Repeat for every Variable in the list until Exit For
Next '//No idea why i need this
End Sub

Answer 1

这个想法是循环通过行号，然后做一个检查，如果在列N和W的值存在，如果它存在，那么它将复制值到2单元格Invuldocument工作表。

Sub CommandButton2_Click()
Const Path As String = "C:Users802435Desktoptest12" 'Assuming that the path don't change, you can declare it as a constant variable

Dim resultWS As Worksheet
Set resultWS = ActiveWorkbook.Worksheets("Resultaten")

Dim invulWS As Worksheet
Set invulWS = ActiveWorkbook.Worksheets("Invuldocument")

Dim i As Long
For i = 2 To 1000
If resultWS.Cells(i, "N").Value <> "" And resultWS.Cells(i, "W").Value <> "" Then
invulWS.Cells(5, "B").Value = resultWS.Cells(i, "N").Value
invulWS.Cells(5, "J").Value = resultWS.Cells(i, "W").Value

Dim FileName As String '//Code below is to save as PDF in every loop
Application.DisplayAlerts = False
FileName = "PRO-" & Range("B5").Value & "-" & Range("D41").Value & ".pdf"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True
Else
Exit For
End If
Next i
End Sub

注意:我没有测试"另存为pdf"的代码是否因为它不在这个问题的范围内。我们绝对鼓励您在分配FileName的代码中完全限定您的范围引用，因为它当前引用的是ActiveSheet，这可能不是您想要的。