• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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
0
gabrielPennyback
Asked:
gabrielPennyback
  • 2
1 Solution
 
byundtCommented:
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

0
 
byundtCommented:
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

Open in new window

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now