CODEWITHSUNDEEP
vbaexcel
Brayheart
Brayheart

Reputation: 167

VBA using like operator with wildcards

I'm trying to compare address data. My current macro compares two columns and inputs "Dropped Data" when they don't match. The problem is that a large number of these values haven't been dropped but integrated into another cell. I want to change my macro to be able to find the missing value using VBA's like operator. For example it would find "Bldg 3" in "9825 Spectrum Dr Bldg 3". I was able to get this code from looking around the web and I'm not sure what range Range("C65536") is selecting.

Edit: I see people are suggesting I use the Instr function which does seem to do what I want to do. I'm not sure how I would get it to work in my macro/ get it to reference the correct cells. It also (from what I understand) returns values equal to the number of characters found. So in the example I gave it would return a value of 6 if you include the space. 

Sub droppeddata()

Application.ScreenUpdating = False

lr = Range("C65536").End(xlUp).Row
For a = lr To 1 Step -1
If Not IsEmpty(Cells(a, 13).Value) And IsEmpty(Cells(a, 19)) Then
Cells(a, 10).Select
Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "N"
Cells(a, 11).Select
Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Dropped Data"
End If
Next a

Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 750

Answers (1)

Vegard
Vegard

Reputation: 4882

Your current macro doesn't compare anything the way you want it to, it just checks whether or not two columns are empty.

You haven't been very specific with what you are trying to do, so this code is done with a bit of guess-work:

Sub droppeddata()
    Dim lr As Long ' Declare the variable
    lr = Range("C65536").End(xlUp).Row ' Set the variable
    ' lr now contains the last used row in column C

    Application.ScreenUpdating = False

    For a = lr To 1 Step -1
        If IsEmpty(Cells(a, 19)) Or InStr(1, Cells(a, 13).Value, Cells(a, 19).Value, vbTextCompare) > 0 Then
        ' If Cells(a, 19) is empty OR
        ' Search Cells(a, 13) for the value contained in Cells(a, 19)
        ' If INSTR returns a match greater than 0, it means the string we're looking for is present
        ' Enter the loop if either condition is true

            ' In this section, avoiding SELECT is preferable. Working directly on the ranges is good.
            With Cells(a, 10)
                .NumberFormat = "General"
                .Value = "N"
            End With

            With Cells(a, 11)
                .NumberFormat = "General"
                .Value = "Dropped Data"
            End With
        End If
    Next a

    Application.ScreenUpdating = True
End Sub

Change the ranges/cells to your need - the current ones aren't meant to work, I merely guessed based on your existing code.

Upvotes: 1

Related Questions