Link to home
Start Free TrialLog in
Avatar of John Carney
John CarneyFlag for United States of America

asked on

VBA that will find a value or variable in a multi-row block of cells

I need to find value x in this range [M62:AG66]. Is there a built in function like that?

Something like:   x = Range("M62:AG62").find (What:= [A4])
But Multi-Row:    x = Range("M62:AG66").find (What:= [A4])

If not, is there an even more compact way than this code to get it to search down, then across? 
Dim cel As Range, FindRng As Range, x As Long
For Each cel In [M62:AG62]
cel.Select
    Set FindRng = Range(cel, cel.Offset(4)) 
    For Each cel2 In FindRng
    If cel2 = [A4] Then
        x = cel.Column - 11
        GoTo here
    End If
    Next cel2
Next cel
here:

Open in new window


Thanks,
John
Avatar of byundt
byundt
Flag of United States of America image
John,
You could use the Find method as shown in the snippet below.

Brad
Sub FindIt2()
Dim cel As Range, FindRng As Range, FindVal As Range, x As Long
Set FindRng = Range("M62:AG62").Resize(5)
Set FindVal = Range("A4")
Set cel = FindRng.Find(FindVal, LookAt:=xlWhole, SearchOrder:=xlByColumns)
If Not cel Is Nothing Then
    x = cel.Column - 11
    cel.Select
    'here would be here
End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John Carney
John Carney
Flag of United States of America image

ASKER

... and his eyes were opened.  Thanks, Brad! Great to hear from you.   ~ John