Solved

Excel VBA Find function to search specific range only

Posted on 2012-03-24
5
333 Views
Last Modified: 2012-03-24
I have a piece of code which succesfully searches for a string and returns the row but I need to refine it so that it only searches the specified range from top to bottom.  If nothing is found then it should return "".

check and x are defined further up...

error1 = Cells.Find(What:=check, After:=Range("A" & x), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Row

Open in new window

0
Comment
Question by:simonwait
  • 2
  • 2
5 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
Comment Utility
What is the specified range?

If we assume it's A1:A100 you could search only that range like this.
Set rngFnd =  Range("A1:A100").Find(What:=check, After:=Range("A" & x), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

If rngFnd Is Nothing Then
    error1 = ""
Else
    error1 = rngFnd.Row

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
FYI - the AFTER range is the LAST cell that is searched.  If order is important, you have to specify the last cell in the range you want to search, not the first.  But, again, only if search order is important.

Cheers.

Dave
0
 
LVL 18

Expert Comment

by:p912s
Comment Utility
If you want it to only search the selected cells you could change your code like this.

error1 = Selection.Find(What:=check, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Row


HTH

Scot
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Dave has a good point, and I was wondering what to do with the After argument.

If you wanted to search the range I showed in the example starting at the first cell, A1, the code would look like this.
Set rngFnd =  Range("A1:A100").Find(What:=check, After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

If rngFnd Is Nothing Then
    error1 = ""
Else
    error1 = rngFnd.Row

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
imnorie - that particular find would search Range("A1") as the LAST parameter, not the first.  The AFTER (I learned recently) is the LAST element to be tested.  Not that it matters, UNLESS, order is important.

Cheers,

Dave
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now