Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Excel VBA Find function to search specific range only

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
simonwait
Asked:
simonwait
  • 2
  • 2
1 Solution
 
NorieData ProcessorCommented:
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
 
dlmilleCommented:
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
 
p912sCommented:
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
 
NorieData ProcessorCommented:
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
 
dlmilleCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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