Excel VBA Range.Find not finding occurrence

[lexxie2013]

Hi all. Pls could someone explain what I'm doing wrong. If I have 'Yes' in Cell A1 and 'Yes' in any other cell in Column A, the MatchVenue.Row returns the second instance rather than the first. Is there an optional header parameter that I've overlooked. I've looked at Microsoft documentation but failed to find an answer. Any help would be much appreciated.

<code>
Dim MatchVenue As Range
VenueName = "Yes"
Set MatchVenue = ThisWorkbook.Sheets("Sheet1").Columns(1).Find(What:=VenueName, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
MsgBox (MatchVenue.Row)
</code>

Thanks,

Steve.

 

[mole999]

have you tried xlpart instead of xlwhole

 

[dmt32]

Hi,
You have omitted the After argument so the search starts in the upper left hand corner of the range. Your search begins AFTER this range & as a match has been found in the range, your code returns that result.

This is fully explained in the VBA helpfile:
https://msdn.microsoft.com/en-us/library/bb178845(v=office.12).aspx

If you want to return all matches in your range, you would need to include FindNext Method which again, you can read about in the VBA helpfile which gives a working example.
https://msdn.microsoft.com/en-us/library/office/ff196143(v=office.15).aspx

Hope Helpful

Dave

 

[lexxie2013]

Thanks Dave,

I'd overlooked the After argument in the documentation. That's certainly given me a solution. Ta.