Error 1004: Unable to get the Search property of the WorksheetFunction class-CodePudding

I am repeatedly getting error 1004: Unable to get the Search property of the WorksheetFunction class for the code below. Basically, I am trying to ascertain if each cell in a specified range contains a value that meets a criteria - if this criteria is met, then a category should be assigned to the value in another cell in the same worksheet:

Option Explicit

Sub Lookup_Category()

Dim Cell As Range, Narration As Range
Dim Counter As Integer

Set Narration = ThisWorkbook.ActiveSheet.Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)

For Each Cell In Narration

    Select Case Cell
            Case Application.WorksheetFunction.Search("LICIOUS", Cell) > 1
                 Cells(Cell.Row, 8) = "Licious"
            Case Application.WorksheetFunction.Search("BILLDESK.ELECTRICITY", Cell) > 1
                 Cells(Cell.Row, 8) = "Electricity"
            Case Application.WorksheetFunction.Search("PLAYSTATIONNETWO", Cell) > 1
                 Cells(Cell.Row, 8) = "PlayStation"
            Case Application.WorksheetFunction.Search("NEFT CR", Cell) > 1
                 Cells(Cell.Row, 8) = "Inbound Transfers"
            Case Application.WorksheetFunction.Search("IMPS", Cell) > 1
                 Cells(Cell.Row, 8) = "Inbound Transfers"
            Case Application.WorksheetFunction.Search("IB FUNDS TRANSFER CR", Cell) > 1
                 Cells(Cell.Row, 8) = "Inbound Transfers"
            Case Application.WorksheetFunction.Search("IB BILLPAY DR-HDFCYA-463917XXXXXX5057", Cell) > 1
                 Cells(Cell.Row, 8) = "Credit Card Payment"
            Case Application.WorksheetFunction.Search("POS 416021XXXXXX5159 SWIGGY", Cell) > 1
                 Cells(Cell.Row, 8) = "Dining"
            Case Application.WorksheetFunction.Search("POS 416021XXXXXX5159 SWIGGY DASH", Cell) > 1
                 Cells(Cell.Row, 8) = "Groceries"
            Case Application.WorksheetFunction.Search("UPI-VODAFONE IDEA LTD-VILPOSKAR", Cell) > 1
                 Cells(Cell.Row, 8) = "Mobile Bill"
            Case Application.WorksheetFunction.Search("UPI-SS LIQOURS", Cell) > 1
                 Cells(Cell.Row, 8) = "Alcohol"
            Case VBA.Left(Cell.Value, 3) = "REV"
                 Cells(Cell.Row, 8) = "Refunds"
            Case Else
                 Cells(Cell.Row, 8) = ""
    End Select
 
Next Cell
 
End Sub

CodePudding user response：

This doesn't need to be VBA, and I'd recommend against VBA if it's not necessary. Say you have a data setup like this:

Column B is the text you're searching against, and column H is where you want the output (to match what the code does). I have created a table in columns M and N (though it could be anywhere, even on a different sheet) that contains what you're looking for and what a match should return.

In cell H2 and copied down is this formula:

=IF(LEFT(B2,3)="REV","Refund",IFERROR(INDEX($N$2:$N$12,MATCH(999,SEARCH($M$2:$M$12,B2))),""))

If it absolutely must be VBA, then I recommend using Select Case True against the Like operator, like so:

Sub Lookup_Category()
    
    Dim wb As Workbook:         Set wb = ThisWorkbook
    Dim ws As Worksheet:        Set ws = wb.ActiveSheet
    Dim rNarration As Range:    Set rNarration = ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp))
    If rNarration.Row < 2 Then Exit Sub 'No data in range

    Dim rCell As Range, rTarget As Range, sValue As String
    For Each rCell In rNarration.Cells
        Set rTarget = ws.Cells(rCell.Row, 8)
        sValue = UCase(rCell.Value)
        Select Case True
            Case sValue Like "*LICIOUS*":                               rTarget.Value = "Licious"
            Case sValue Like "*BILLDESK.ELECTRICITY*":                  rTarget.Value = "Electricity"
            Case sValue Like "*PLAYSTATIONNETWO*":                      rTarget.Value = "PlayStation"
            Case sValue Like "*NEFT CR*":                               rTarget.Value = "Inbound Transfers"
            Case sValue Like "*IMPS*":                                  rTarget.Value = "Inbound Transfers"
            Case sValue Like "*IB FUNDS TRANSFER CR*":                  rTarget.Value = "Inbound Transfers"
            Case sValue Like "*IB BILLPAY DR-HDFCYA-463917XXXXXX5057*": rTarget.Value = "Credit Card Payment"
            Case sValue Like "*POS 416021XXXXXX5159 SWIGGY DASH*":      rTarget.Value = "Groceries"
            Case sValue Like "*POS 416021XXXXXX5159 SWIGGY*":           rTarget.Value = "Dining"
            Case sValue Like "*UPI-VODAFONE IDEA LTD-VILPOSKAR*":       rTarget.Value = "Mobile Bill"
            Case sValue Like "*UPI-SS LIQOURS*":                        rTarget.Value = "Alcohol"
            Case Left(sValue, 3) = "REV":                               rTarget.Value = "Refunds"
            Case Else:                                                  rTarget.ClearContents
        End Select
    Next rCell
 
End Sub