Find In VBA Code: Find Exact Match

  • Hi

    I'm using the below VBA 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?


  • 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?).

  • To Find Exact Match in VBA

    you need to add the LookAt argument :

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


    Edited once, last by Carim: Added Code Tags ().

  • 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!!


  • Re: .find To Find Exact Match

    See Find Method Take special note of


    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


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!