x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 376

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:

Thanks,
John
0
gabrielPennyback
• 2
1 Solution

Commented:
John,
You could use the Find method as shown in the snippet below.

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
0

Commented:
One fine point with the Find method is that the search starts with the cell after the top left cell in the range. So I should correct the code to this:
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 = IIf(FindVal.Value = FindRng.Cells(1, 1).Value, FindRng.Cells(1, 1), 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
0

Reliability Business Tools Analyst IIAuthor Commented:
... and his eyes were opened.  Thanks, Brad! Great to hear from you.   ~ John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.