CODEWITHSUNDEEP
excelvba
Ren
Ren

Reputation: 1

VBA - Categorization using "Like"

I'm creating a Macro to do almost exactly what is outlined here:

Excel/Categorization Macro(formula) based on string of text containing keywords

My question is that in the code from the example above Like "" is used to check to see if the Description matches a keyword and if it does then it pulls the corresponding category name. In my case, I don't currently have keywords for every possible category (but will eventually have them as I collect more transaction data), meaning some of the cells in my keyword column are blank and the way the above code is written it considers patternfound = true when it encounters an empty cell. How do I alter the If statement with "Like" or something similar so that it skips over a cell if it's completely blank and only provides a match when there are some characters (that match) in the cell?

I've found a work around by putting "N/A" in the empty cells but I'd rather not do that. Here is my code:

Sub Categorize()

Dim lastrow As Long, lastrow2 As Long
Dim i As Integer, j As Integer
Dim PatternFound As Boolean

Call speedup

lastrow = Sheets("Categorization").Range("B" & Rows.Count).End(xlUp).Row
lastrow2 = Sheets("Cleaned Spend Data").Range("C" & Rows.Count).End(xlUp).Row


For i = 4 To lastrow2

    PatternFound = False

    j = 1

    Do While PatternFound = False And j < lastrow

        j = j + 1

        If UCase(Sheets("Cleaned Spend Data").Range("B" & i).Value) Like "*" & UCase(Sheets("Categorization").Range("B" & j).Value) & "*" Then
            Sheets("Cleaned Spend Data").Range("D" & i).Value = Sheets("Categorization").Range("A" & j).Value
            PatternFound = True
        End If

      Loop

    Next i

    Call normal

End Sub

Thanks!

Upvotes: 0

Views: 166

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

You can test for an empty cell...

Also - your code could be cleaner using a couple of variables for your worksheets.

Sub Categorize()

    Dim lastrow As Long, lastrow2 As Long
    Dim i As Integer, j As Integer
    Dim PatternFound As Boolean, shtCat As Worksheet, shtCleaned As Worksheet
    Dim v, t

    Set shtCat = Sheets("Categorization")
    Set shtCleaned = Sheets("Cleaned Spend Data")

    Call speedup

    lastrow = shtCat.Range("B" & Rows.Count).End(xlUp).Row
    lastrow2 = shtCleaned.Range("C" & Rows.Count).End(xlUp).Row

    For i = 4 To lastrow2
        v = UCase(UCase(shtCleaned.Range("B" & i).Value))
        For j = 1 To lastrow
            t = UCase(Sheets("Categorization").Range("B" & j).Value)
            If Len(t) > 0 And v Like "*" & t & "*" Then
                shtCleaned.Range("D" & i).Value = shtCat.Range("A" & j).Value
                Exit For
            End If
        Next j
    Next i

    Call normal

End Sub

Upvotes: 1

Related Questions