Multiple Match - Find VBA

[MUKESHY12390]

Hi All,

got situation --

I have one column (A) and header start from very first row (1) And after several rows header starts again.

so whenever I use Find method it give me second match doesn't give first match.

Example
'''''I enter text "EmpID" in two cell A1 and A2 and excel gives me only second match 
Sheet1.Activate


    Columns(1).Find(What:="EmpID", After:=Range("A1"), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
     MsgBox ActiveCell.Address


End Sub

 

[Marcelo Branco]

Maybe

Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Columns(1).Find(What:="EmpID", After:=Range("A" & LR), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
MsgBox ActiveCell.Address

M.

 

[MUKESHY12390]

you nailed it.... thank you so much.


I had some other way to resolve this issue. but yours solution is really awesome.

 

[Marcelo Branco]

You are welcome. Glad to help.

By the way, if you do need to find all instances of EmpID take a look at
http://www.cpearson.com/excel/findall.aspx

M.

 

[MUKESHY12390]

thanks again.

 

[Rick Rothstein]

Hi All,

got situation --

I have one column (A) and header start from very first row (1) And after several rows header starts again.

so whenever I use Find method it give me second match doesn't give first match.

Example
'''''I enter text "EmpID" in two cell A1 and A2 and excel gives me only second match 
Sheet1.Activate


    Columns(1).Find(What:="EmpID", [B][COLOR="#FF0000"]After:=Range("A1")[/COLOR][/B], LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
     MsgBox ActiveCell.Address


End Sub

Just so you understand the root of the problem you had... what I highlighted in red is why you had the problem... the search takes place after cell A1, so the headers are not looked at first. The change Marcelo gave you makes the search start after the last cell in the range which force the Find function to wrap around to the beginning of the range for the start of the search.

 

[MUKESHY12390]

yes exactly, Even if remove "After" criteria by default it starts from A1 I guess.


Thanks!

 

[Rick Rothstein]

yes exactly, Even if remove "After" criteria by default it starts from A1 I guess.

Yes, the default, when omitted, after which the search starts is the upper left cell of the specified range.