Find In VBA Code: Find Exact Match

jamesparker_1 · Oct 9th 2006

Hi

I'm using the below VBA code:

Code

Dim c, DataRow
        With Data
            Set c = .Range("A5:A350").Find(KPI, LookIn:=xlValues)
            If Not c Is Nothing Then
                        DataRow = c.Row
            End If
        End With

Now, my problem is that If KPI is for example = "Favourite Hotel" and if i have data that looks like :

Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham

Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).

How do i make it search for an exact match?

James

Richie(UK) · Oct 9th 2006

Re: .find To Find Exact Match

Hi James,

In the VBE place the cursor within the keyword 'Find' and then press F1 to get the Help for using Find (as it applies to Ranges). You will see that there are a number of arguments in addition to the ones you currently have. Take a look at "LookAt" (is there an echo in here?).

**Andy Pope** · Oct 9th 2006

To Find Exact Match in VBA

you need to add the LookAt argument :

Code

Set c = .Range("A5:A350").Find(KPI, LookIn:=xlValues,lookat:=xlwhole)

jamesparker_1 · Oct 9th 2006

Re: .find To Find Exact Match

Yeah, that is what i needed! Thanks! Good tip about the F1 Help too. I think that'll help me solve more problems myself!

Thanks again!!

James

Dave Hawley · Oct 10th 2006

Re: .find To Find Exact Match

See Find Method Take special note of

Quote

It is very important to understand that the settings LookIn, LookAt and SearchOrder are saved each time the Find Method is used. For this reason one should always specify these settings explicitly each and every time you use the Find Method. If you don't, you run the risk of using the Find Method with settings you were not aware of.

In addition, Find will always start from the Active Cell and if the Active Cell is not withing your set range, it will fail. In your case you will also need

Code

After:=.Range("A5")

Find In VBA Code: Find Exact Match

Participate now!

Share

Users Viewing This Thread