Syntax Error 1004

Hi,

I am trying to create a macro with VBA using the following formula:

=IF(ISNUMBER(SEARCH("coles",F2)),"Groceries",IF(ISNUMBER(SEARCH("woolworths",F2)),"Groceries",IF(ISNUMBER(SEARCH("pharmacy",F2)),"Pharmacy",IF(ISNUMBER(SEARCH("bupa",F2)),"Health Insurance",IF(ISNUMBER(SEARCH("tcs",F2)),"Hair",IF(ISNUMBER(SEARCH("medical",F2)),"Medical",IF(ISNUMBER(SEARCH("interest",F2)),"Interest",IF(ISNUMBER(SEARCH("xero",F2)),"Accounting",IF(ISNUMBER(SEARCH("top up",F2)),"CC Exp",IF(ISNUMBER(SEARCH("thomas",F2)),"Income",IF(ISNUMBER(SEARCH("telstra",F2)),"Phone",IF(ISNUMBER(SEARCH("ep loan",F2)),"EP Exp",IF(ISNUMBER(SEARCH("Tville loan",F2)),"Tville Exp",IF(ISNUMBER(SEARCH("ergon",F2)),"Power",IF(ISNUMBER(SEARCH("unit  51",F2)),"Tville Inc",IF(ISNUMBER(SEARCH("car",F2)),"Car Exp",IF(ISNUMBER(SEARCH("townsville city",F2)),"Tville Exp",IF(ISNUMBER(SEARCH("LSC",F2)),"EP Exp",""))))))))))))))))))

When I try to run the macro it comes up with a syntax error 1004.

If I reduce the formula to

=IF(ISNUMBER(SEARCH("coles",F2)),"Groceries",IF(ISNUMBER(SEARCH("woolworths",F2)),"Groceries",IF(ISNUMBER(SEARCH("pharmacy",F2)),"Pharmacy",IF(ISNUMBER(SEARCH("bupa",F2)),"Health Insurance",IF(ISNUMBER(SEARCH("tcs",F2)),"Hair",IF(ISNUMBER(SEARCH("medical",F2)),"Medical",""))))))

it works fine.

It is as though VBA can only handle up to a certain formula length.

Could anyone help?

Cheers
T

I could not re-create the same error (pitty you've not posted a dummy sample workbook), but just in case:
use such formula instead, it's much shorter and less prone for errors:

Formula:

=IFERROR(VLOOKUP("*"&F2&"*",{"coles","Groceries";"woolworths","Groceries";"pharmacy","Pharmacy";"bupa","Health Insurance"},2,0),"")

so basically, you put yout pairs matchedvalue-returnedvalue separated by comma and between each pair put semicolon. (if it's not working with your version of excel - try reversing: comma between pairs and elements in pair separated with semicolon)

Hi Kaper,

Thanks for your response. I tried your formula and it doesn't return anything.

I have attached a dummy workbook.

Sheet 1 has your formula applied in column D.
Sheet 2 has your formula with ; & , swapped.
Sheet 3 has my formula.
Sheet 4 I recorded a macro with my formula and I get a message to debug then the syntax error.

Here is the VBA script - It highlights from ActiveCell to the end of the formula in yellow.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""coles"",RC[2])),""Groceries"",IF(ISNUMBER(SEARCH(""woolworths"",RC[2])),""Groceries"",IF(ISNUMBER(SEARCH(""pharmacy"",RC[2])),""Pharmacy"",IF(ISNUMBER(SEARCH(""bupa"",RC[2])),""Health Insurance"",IF(ISNUMBER(SEARCH(""tcs"",RC[2])),""Hair"",IF(ISNUMBER(SEARCH(""medical"",RC[2])),""Medical"",IF(ISNUMBER(SEARCH(""interest"",RC[2])),""Interest"",IF" & _
        "R(SEARCH(""xero"",RC[2])),""Accounting"",IF(ISNUMBER(SEARCH(""top up"",RC[2])),""CC Exp"",IF(ISNUMBER(SEARCH(""thomas"",RC[2])),""Income"",IF(ISNUMBER(SEARCH(""telstra"",RC[2])),""Phone"",IF(ISNUMBER(SEARCH(""ep loan"",RC[2])),""EP Exp"",IF(ISNUMBER(SEARCH(""Tville loan"",RC[2])),""Tville Exp"",IF(ISNUMBER(SEARCH(""ergon"",RC[2])),""Power"",IF(ISNUMBER(SEARCH(""unit" & _
        "[2])),""Tville Inc"",IF(ISNUMBER(SEARCH(""car"",RC[2])),""Car Exp"",IF(ISNUMBER(SEARCH(""townsville city"",RC[2])),""Tville Exp"",IF(ISNUMBER(SEARCH(""LSC"",RC[2])),""EP Exp"",""""))))))))))))))))))"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D20"), Type:=xlFillDefault
    Range("D2:D20").Select
    Range("F2").Select
End Sub

I am interested if you can replicate my issue.

Cheers
T

Hi,

With sample data it's obvious that my formula is wrong. So discard it.

As the problem with the formula to be inserted by the VBA - look at front and end of sub strings you splitted thye formula ther are incomplete

    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER( ... ""Interest"",IF" & _
        "R(SEARCH(""xero"" ... SEARCH(""unit" & _
        "[2])),""Tville Inc"",... ""EP Exp"",""""))))))))))))))))))"

so VBA could not insert such wrong formula
Right notation could be for instance:

    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""coles"",RC[2])),""Groceries"",IF(ISNUMBER(SEARCH(""woolworths"",RC[2])),""Groceries""," & _
        "IF(ISNUMBER(SEARCH(""pharmacy"",RC[2])),""Pharmacy"",IF(ISNUMBER(SEARCH(""bupa"",RC[2])),""Health Insurance""," & _
        "IF(ISNUMBER(SEARCH(""tcs"",RC[2])),""Hair"",IF(ISNUMBER(SEARCH(""medical"",RC[2])),""Medical""," & _
        "IF(ISNUMBER(SEARCH(""interest"",RC[2])),""Interest"",IF(ISNUMBER(SEARCH(""xero"",RC[2])),""Accounting""," & _
        "IF(ISNUMBER(SEARCH(""top up"",RC[2])),""CC Exp"",IF(ISNUMBER(SEARCH(""thomas"",RC[2])),""Income""," & _
        "IF(ISNUMBER(SEARCH(""telstra"",RC[2])),""Phone"",IF(ISNUMBER(SEARCH(""ep loan"",RC[2])),""EP Exp""," & _
        "IF(ISNUMBER(SEARCH(""Tville loan"",RC[2])),""Tville Exp"",IF(ISNUMBER(SEARCH(""ergon"",RC[2])),""Power""," & _
        "IF(ISNUMBER(SEARCH(""unit"",RC[2])),""Tville Inc"",IF(ISNUMBER(SEARCH(""car"",RC[2])),""Car Exp""," & _
        "IF(ISNUMBER(SEARCH(""townsville city"",RC[2])),""Tville Exp"",IF(ISNUMBER(SEARCH(""LSC"",RC[2])),""EP Exp"",""""))))))))))))))))))"

Having sample file available, I can propose such (sample - may be not most effective, but I hope easy to read) code to do this without formulas.

Sub fill_with_descriptions()
Const keywordslist = "coles,woolworths,pharmacy,bupa"
Const descriptionslist = "Groceries,Groceries,Pharmacy,Health Insurance"
Dim i As Integer, cell As Range, keyword As Variant, description As Variant
keyword = Split(keywordslist, ",")
description = Split(descriptionslist, ",")
If UBound(keyword) <> UBound(description) Then
  MsgBox "Number of items in keywords and descriptions lists differ", vbCritical
  Exit Sub
End If
Application.ScreenUpdating = False
For Each cell In Range(Range("F1"), Cells(Rows.Count, "F").End(xlUp))
  For i = LBound(keyword) To UBound(keyword)
    If InStr(1, cell, keyword(i), vbTextCompare) <> 0 Then
      cell.Offset(0, -2) = description(i)
      Exit For 'if found do not check other possibilities
    End If
  Next i
Next cell
End Sub