CODEWITHSUNDEEP
excelvbafind
Raúl
Raúl

Reputation: 159

VBA Excel find exact value of a cell, not just part of it

I have the following code:

Set User = AD_USERS.Range("D:D").Find(What:=wVal)

Where wVal is the value I'm looking for. It's the name of a user that can be like "Ecr484348" or "gh8644". My question is, how can I use find to look for a total coincidence when i'm looking for the name of a user?

I know that if I use xlWhole it looks for a whole coincidence but If wVal = "Ecr" is going to give me that it found "Ecr484348" and I don't want that. I want that only if you do wVal = "Ecr484348" gives you that it found the value, I mean I don't want it to work only using some part of the user name.

Sorry if anything is not well explained, I'll answer if there are any questions. Thank you so much for your answers!

PS: If I use xlWhole like in the following code:

Set User = AD_USERS.Range("D:D").Find(What:=wVal, LookAt:=xlWhole)

It throws me an error 9, and I don't know why.

PS2: I add the whole code here:

Dim wrdTbl As Table
    'Set the Word table
    With ActiveDocument
        If ActiveDocument.Tables.Count >= 1 Then
            Set wrdTbl = .Tables(InputBox("Table # to copy? There are " & .Tables.Count & " tables to choose from."))
        End If
    End With

    Dim AD_UsersPath As String
    AD_UsersPath = "C:\Users\" & Environ("Username") & "\Desktop\Comparar Columnas VBA\Animales.xlsx"
    Dim AD_USERS As Object
    Set AD_USERS = CreateObject("Excel.Application")
    AD_USERS.Visible = False
    AD_USERS.Application.Workbooks.Open AD_UsersPath
    
    Dim LastRow As Integer
    LastRow = wrdTbl.Columns(1).Cells.Count 

    Dim I As Integer
    For I = 1 To LastRow
        wVal = wrdTbl.Cell(I + 1, 1)
        wVal = Left(wVal, Len(wVal) - 2) 
        Set User = AD_USERS.Range("D:D").Find(What:=wVal)
        If User Is Nothing Then
            wrdTbl.Cell(I + 1, 1).Shading.BackgroundPatternColor = wdColorRed 
        Else
            wrdTbl.Cell(I + 1, 1).Shading.BackgroundPatternColor = wdColorWhite 
        End If
    Next I
    
    AD_USERS.Quit
    Set AD_USERS = Nothing

Upvotes: 0

Views: 2084

Answers (2)

Raúl
Raúl

Reputation: 159

As @Foxfire And Burns And Burns suggested I used CountIf at the end so it worked better and I solved my error using Find. The line is now:

User = AD_USERS.Application.WorksheetFunction.CountIf(AD_USERS.ActiveWorkbook.ActiveSheet.Range("D:D"), wVal)

Thank you everyone for your answers!

Upvotes: 1

Super Symmetry
Super Symmetry

Reputation: 2875

Change the line Set User = AD_USERS.Range("D:D").Find(What:=wVal) to

Set User = AD_USERS.Range("D:D").Find(What:=wVal, LookAt:=1)

When you are using late binding to create and excel application from word vba, then excel constants (e.g. xlWhole) are not defined by default. They are only defined if you add the excel reference to your project. You can either define it yourself with Dim xlWhole As Integer: xlWhole = 1 or just use the value 1 whenever you need to use this constant.

Upvotes: 3

Related Questions