CODEWITHSUNDEEP
vbaexcel
AltoidsBenefitsH
AltoidsBenefitsH

Reputation: 55

Excel VBA: Iterating over Multiple Ranges

I am currently writing a code that automates goalseek but I ran into syntax issues.

I declared 3 variables:

Dim X as Range
Dim Y as Range
Dim Z as Range

Set X as Range(....)
Set Y as Range(....)
Set Z as Range(....)

For each cell in X,Y,Z
X.GoalSeek Goal:=Y, ChangingCell:=Z
Next cell in X,Y,Z

This is pseudo code but I want it to iterate one by one in each of the cells in range X,Y,Z.

These are column ranges.

Question: How do I have each cell in range X,Y,Z iterate to put into the goalseek function? So if each of the ranges had 5 cells, it would run 5 times.

Upvotes: 2

Views: 5189

Answers (1)

MikeD
MikeD

Reputation: 8941

  1. For ... Each doesn't allow comma seperated arguments after in, so you must present "one" range to it ...

  2. if you want to say For Each cell ... then you must decompose ranges into cells using their .Cells property

  3. inside the For ... Each you then ought to work with the loop variable (cell) instead X, Y, Z

Example

Sub Test()
Dim X As Range, Y As Range, Z As Range
Dim C As Range

    Set X = [A1]
    Set Y = [B1:B2]
    Set Z = [C1:C3]

    For Each C In Union(X.Cells, Y.Cells, Z.Cells)
        MsgBox C.AddressLocal
    Next C
End Sub

Alternative syntax for the Union(...) would be

For Each C In Union(X, Y, Z).Cells

which probably makes it even clearer that you're tieing together 3 ranges and then address each of their cells ... matter of taste maybe

EDIT under the assumption you want to form TRIPLES of your (equal sized) ranges and feed them into a single function, calculate offsets for ranges Y and Z onto X

example

Sub Test()
Dim X As Range, Y As Range, Z As Range
Dim C As Range
Dim OYR As Long, OYC As Long, OZR As Long, OZC As Long

    Set X = [A1:A5]
    Set Y = [B3]        'no need to provide full range, starting point will do
    Set Z = [C5]

    ' calculating offsets
    OYR = Y(1, 1).Row - X(1, 1).Row + 1
    OYC = Y(1, 1).Column - X(1, 1).Column + 1

    OZR = Z(1, 1).Row - X(1, 1).Row + 1
    OZC = Z(1, 1).Column - X(1, 1).Column + 1

    ' iterate thru all cells of X and their equally ofsett partners in Y and Z
    For Each C In X.Cells
        MsgBox C.AddressLocal & " " & C(OYR, OYC).AddressLocal & " " & C(OZR, OZC).AddressLocal
    Next C

End Sub

Upvotes: 1

Related Questions