CODEWITHSUNDEEP
vbaexcel
willi
willi

Reputation: 33

Passing Range Variables

Further to my earlier question, I have defined several ranges ,

for example:

Sheets(“Customers”).Activate
    Set MYR1 = Range(Cells(1,  1), Cells(1,  25))
    Set MYR2 = Range(Cells(5,  1), Cells(5,  25))
    Set MYR2 = Range(Cells(16,  1), Cells(16,  25))

I have also set up the following sub routine

Sub Findany(rngToSearchIn As Range)
    Set foundrange = rngToSearchIn.Find(what:=i)
        If foundrange Is Nothing Then
                Do Something
        Else
                Do Something Else
        End If
    End Sub

My problem now is how can I have the calling sub routine update the range name each time it calls the Findany sub routine

eg

Call Findany(MYR1)




Call Findany((MYR2)

Upvotes: 0

Views: 55

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Dim arr(1 to 3) As Range, i

With Sheets(“Customers”)
    Set arr(1) = .Range(.Cells(1,  1), .Cells(1,  25))
    Set arr(2) = .Range(.Cells(5,  1), .Cells(5,  25))
    Set arr(3) = .Range(.Cells(16,  1), .Cells(16,  25))
End With

For i = 1 to 3
    Findany arr(i)
Next i

Upvotes: 1

Related Questions