CODEWITHSUNDEEP
vbaexcel
FancyDolphin
FancyDolphin

Reputation: 457

VBA: end of statement Error

Silly question but I don't understand this error so if someone can tell me why this is happening and how to fix it I'd appreciate it. I have a formula that works in the excel sheet. In VBA all I'm doing is going to put this formula in and fill it down to the last filled row in the adjacent column. The formula I have is as follows:

Compile error, Expected: end of statement (It does this at "FIG" portion) 

Range("C1").Formula = "=IF(B1="","",IF(ISNUMBER(SEARCH("*FIG*",B1)),"FIG", IF(ISNUMBER(SEARCH(" *SF* ",B1)),"SF",IF(ISNUMBER(SEARCH(" *AF* ",B1)),"AF&L", IF((ISNUMBER(SEARCH(" * IB * ",B1)) ),IF(ISNUMBER(SEARCH(" * ASIA * ",B1)),"IBAsia","IBexA"))))))"       
        Range("C1").AutoFill Destination:=Range("C1:C" & (ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row))

Upvotes: 1

Views: 245

Answers (1)

user4039065
user4039065

Reputation:

When using quotes within a quoted string, all of the quotes need to be doubled up except the 'wrapping' quotes. In the following I've doubled up the quotes and removed some spaces that my experience tells me that the VBE mistakenly inserts when trying to make sense of a bad string.

Dim lr As Long
With ActiveSheet
    lr = .Cells(Rows.Count, 2).End(xlUp).Row
    With .Range(.Cells(1, 3), .Cells(lr, 3))
        .Formula = "=IF(B1="""", """", " & _
            "IF(ISNUMBER(SEARCH(""*FIG*"", B1)), ""FIG"", " & _
            "IF(ISNUMBER(SEARCH(""*SF*"", B1)), ""SF"", " & _
            "IF(ISNUMBER(SEARCH(""*AF*"", B1)), ""AF&L"",  " & _
            "IF((ISNUMBER(SEARCH(""*IB*"", B1))),  " & _
              "IF(ISNUMBER(SEARCH(""*ASIA*"", B1)), ""IBAsia"", ""IBexA""))))))"
    End With
End With

The above produces the following formula in the ActiveSheet's column C from row 1 to the extents of column B,

=IF(B1="", "", IF(ISNUMBER(SEARCH("*FIG*", B1)), "FIG", IF(ISNUMBER(SEARCH("*SF*", B1)), "SF", IF(ISNUMBER(SEARCH("*AF*", B1)), "AF&L",  IF((ISNUMBER(SEARCH("*IB*", B1))),  IF(ISNUMBER(SEARCH("*ASIA*", B1)), "IBAsia", "IBexA"))))))

If that formula is correct, it might be able to be simplified but confirmation on the formula would be necessary before making any suggestions to that end.

Upvotes: 2

Related Questions