Passing Range Variables

Question

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)

Tim Williams · Accepted Answer · 2015-06-22 04:53:10Z

1

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

answered Jun 22, 2015 at 4:53

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

Passing Range Variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related