[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-04-17
3
Medium Priority
?
368 Views
Last Modified: 2014-04-18
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
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 40008174
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
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 40008181
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
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 40009116
... and his eyes were opened.  Thanks, Brad! Great to hear from you.   ~ John
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question