CODEWITHSUNDEEP
excelvba
Huz
Huz

Reputation: 13

VBA Adding another variable to a loop

I have a method CreatePage(). I would like to loop through variables to pass through the method. I have managed to get it working for one variable:

Sub main()

   Dim rng As Range
   Dim aSht As Worksheet
    
   Set aSht = ThisWorkbook.Sheets("Sheet1")
    
   For Each rng In aSht.Range("A5:A8")
        CreatePage(rng)
    Next rng

End Sub

I need it to loop as follows:
CreatePage("A5", "C6")
CreatePage("A6", "C7")
CreatePage("A7", "C8"), ...

I am stuck on how to pass through two variables. This is where I have got so far, but I don't think I'm going in the right direction:

Sub main()

   Dim rng As Range
   Dim rng2 As Range
   
   Dim aSht As Worksheet
   Dim bSht As Worksheet
    
   Set aSht = ThisWorkbook.Sheets("Sheet1")
   Set bSht = ThisWorkbook.Sheets("Sheet2")
    
   For Each rng In aSht.Range("A5:A8")
        For Each rng2 In bSht.Range("C6:C9")
             CreatePage(rng,rng2)
    Next rng, rng2

End Sub

I have adjusted CreatePage() to hold two variables. I'm puzzled on getting the second variable in the for loop. Can anyone help out?

Upvotes: 0

Views: 66

Answers (2)

chris neilsen
chris neilsen

Reputation: 53146

A nested for loop won't help here. You need to loop one range only and pick the required cell from the other

Sub main()
    Dim rng As Range
    Dim rng2 As Range
   
    Dim aSht As Worksheet
    Dim bSht As Worksheet
    
    Set aSht = ThisWorkbook.Sheets("Sheet1")
    Set bSht = ThisWorkbook.Sheets("Sheet2")
    
    Dim idx As Long
    Set rng = aSht.Range("A5:A8")
    Set rng2 = bSht.Range("C6:C9")
    'You might want to add code here to check the size and shape of your ranges
    For idx = 1 To rng.Cells.Count
        CreatePage rng.Cells(idx), rng2.Cells(idx)
    Next

End Sub

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27269

Just a simple change needed.

   For Each rng In aSht.Range("A5:A8")
        For Each rng2 In bSht.Range("C6:C9")
             CreatePage rng, rng2
        Next rng2
   Next rng

Upvotes: 0

Related Questions