VBA Adding another variable to a loop

Question

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?

chris neilsen · Accepted Answer · 2020-11-25 18:04:18Z

1

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

answered Nov 25, 2020 at 18:04

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

Comments

Scott Holtzman · Accepted Answer · 2020-11-25 17:22:06Z

0

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

edited Nov 25, 2020 at 17:22

answered Nov 25, 2020 at 17:01

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

4 Comments

chris neilsen Over a year ago

Also, drop the () in CreatePage(rng,rng2)

Huz Over a year ago

@ScottHoltzman @chrisneilsen Thanks guys, this has helped alot but I don't think it's fully worked. The cells from rng seem to pass through successfully, but the variables from rng2 are not iterating as expected. It is only passing through the last cell. For example: CreatePage("A5", "C9") CreatePage("A6", "C9") CreatePage("A7", "C9") Either something is wrong with the loop or something in my method has gone wrong.

Huz Over a year ago

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

chris neilsen Over a year ago

@huz I'd say something else is going wrong. This code will call CreatePage 16 times, once for each pair of cells. Might be time to show us CreatePage, probably in an new Q

Collectives™ on Stack Overflow

VBA Adding another variable to a loop

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related