Syntax Error trying to write a formula into a cell with VBA

Question

Not actually familiar with visual basic but used some references to make this.

I would like to ask since I spent an hour figuring out how to solve this, may be I need some suggestions. I am encountering this error in Visual Basic to make macros for excel. At first it was working but after I added some lines the syntax turns red and when run the macro it just have a

Compile Error: Syntax Error

Range("D2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(ISNUMBER(SEARCH(""California"",RC[-2],1)),""California"", 
    IF(ISNUMBER(SEARCH(""Florida"",RC[-2],1)),""Florida"", 
    IF(ISNUMBER(SEARCH(""Texas"",RC[-2],1)),""Texas"", IF(ISNUMBER(SEARCH(""New 
    Mexico"",RC[-2],1)),""New Mexico"", IF(ISNUMBER(SEARCH(""Alaska"",RC[-2],1)),""Alaska"", 
    IF(ISNUMBER(SEARCH(""New Jersey"",RC[-2],1)),""New Jersey"",
    """"))))))&IF(ISNUMBER(SEARCH(""Marikina"",RC[-2],1)),""Marikina"", 
    IF(ISNUMBER(SEARCH(""Maryland"",RC[-2],1)),""Maryland"", 
    IF(ISNUMBER(SEARCH(""Nebraska"",RC[-2],1)),""Nebraska"", 
    IF(ISNUMBER(SEARCH(""Pennsylvania"",RC[-2],1)),""Pennsylvania"",   
    IF(ISNUMBER(SEARCH(""Illinois"",RC[-2],1)),""Illinois"",
    IF(ISNUMBER(SEARCH(""Colorado"",RC[-2],1)),""Colorado"",""""))))))&
    IF(ISNUMBER(SEARCH(""Louisiana"",RC[-2],1)),""Louisiana"", 
    IF(ISNUMBER(SEARCH(""Idaho"",RC[-2],1)),""Idaho"", 
    IF(ISNUMBER(SEARCH(""Hawaii"",RC[-2],1)),""Hawaii"", 
    IF(ISNUMBER(SEARCH(""Vermont"",RC[-2],1)),""Vermont"", 
    IF(ISNUMBER(SEARCH(""West Virginia"",RC[-2],1)),""West Virginia"",           
    IF(ISNUMBER(SEARCH(""Connecticut"",RC[-2],1)), 
    ""Connecticut"","""")))))"
Range("D2").Select
 Selection.AutoFill Destination:=Range("D2:D38"), Type:=xlFillDefault
 Range("D2:D38").Select
 ActiveWindow.SmallScroll Down:=-39
End Sub

You dont have any `"`s for the other lines or continuations and only looks like one `&` to concatenate the lines. https://stackoverflow.com/questions/22854386/how-to-continue-the-code-on-the-next-line-in-vba — Nathan_Sav, Sep 15 '21 at 09:33

Pᴇʜ · Accepted Answer · 2021-09-15T11:39:33.007

You cannot just add line breaks, you need to split the formula into multiple strings and concatenate them with &. Line breaks need to be concatenated with _ in the end of the line.

Range("D2").FormulaR1C1 = _
"=" & _
"IF(ISNUMBER(SEARCH(""California"",RC[-2],1)),""California"", " & _
"IF(ISNUMBER(SEARCH(""Florida"",RC[-2],1)),""Florida""," & _
"IF(ISNUMBER(SEARCH(""Texas"",RC[-2],1)),""Texas""," & _
"IF(ISNUMBER(SEARCH(""New Mexico"",RC[-2],1)),""New Mexico""," & _
"IF(ISNUMBER(SEARCH(""Alaska"",RC[-2],1)),""Alaska""," & _
"IF(ISNUMBER(SEARCH(""New Jersey"",RC[-2],1)),""New Jersey""," & _
"""""))))))&" & _
"IF(ISNUMBER(SEARCH(""Marikina"",RC[-2],1)),""Marikina""," & _
"IF(ISNUMBER(SEARCH(""Maryland"",RC[-2],1)),""Maryland""," & _
"IF(ISNUMBER(SEARCH(""Nebraska"",RC[-2],1)),""Nebraska""," & _
"IF(ISNUMBER(SEARCH(""Pennsylvania"",RC[-2],1)),""Pennsylvania""," & _
"IF(ISNUMBER(SEARCH(""Illinois"",RC[-2],1)),""Illinois""," & _
"IF(ISNUMBER(SEARCH(""Colorado"",RC[-2],1)),""Colorado""," & _
"""""))))))&" & _
"IF(ISNUMBER(SEARCH(""Louisiana"",RC[-2],1)),""Louisiana""," & _
"IF(ISNUMBER(SEARCH(""Idaho"",RC[-2],1)),""Idaho""," & _
"IF(ISNUMBER(SEARCH(""Hawaii"",RC[-2],1)),""Hawaii""," & _
"IF(ISNUMBER(SEARCH(""Vermont"",RC[-2],1)),""Vermont""," & _
"IF(ISNUMBER(SEARCH(""West Virginia"",RC[-2],1)),""West Virginia""," & _
"IF(ISNUMBER(SEARCH(""Connecticut"",RC[-2],1)),""Connecticut""," & _
"""""))))))"

Range("D2").AutoFill Destination:=Range("D2:D38"), Type:=xlFillDefault

You might benefit from reading How to avoid using Select in Excel VBA.

To make the process less repeating create a function that can generate the formula by using a list of the states eg:

Option Explicit

Public Sub Example()
    
    Dim ListOfStates() As Variant
    ListOfStates = Array("California", "Florida", "Texas", "New Mexico", "Alaska", "New Jersey", "Marikina", "Maryland", "Nebraska", "Pennsylvania", "Illinois", "Colorado", "Louisiana", "Idaho", "Hawaii", "Vermont", "West Virginia", "Connecticut")
    
    
    Range("D2").FormulaR1C1 = "=" & Join(CreateFormulaBlocks(ListOfStates), "&") 'join all blocks by &
End Sub

' returns an array of all blocks (each 6 states)
'
Public Function CreateFormulaBlocks(ByVal ListOfStates As Variant, Optional ByVal MaxPerBlock As Long = 6) As Variant
    ' how many blocks do we need
    Dim MaxBlocks As Long
    MaxBlocks = (UBound(ListOfStates) + 1) \ 6  ' note this is no normal division / but an integer division \
    
    ' create array for blocks
    Dim ReturnBlocks() As Variant
    ReDim ReturnBlocks(MaxBlocks - 1) As Variant
    
    ' create blocks
    Dim iBlock As Long
    For iBlock = 0 To MaxBlocks - 1
        Dim BlockStates() As Variant
        ReDim BlockStates(MaxPerBlock - 1) As Variant

        Dim iState As Long
        For iState = 0 To MaxPerBlock - 1
            BlockStates(iState) = ListOfStates(iBlock * 6 + iState)
        Next iState
        
        ' create one block of 6 states
        ReturnBlocks(iBlock) = CreateFromulaBlock(BlockStates)
    Next iBlock
    
    ' return all blocks as array
    CreateFormulaBlocks = ReturnBlocks
End Function



' returns one block of 6 states
' eg IF(ISNUMBER(SEARCH("California",RC[-2],1)),"California", IF(ISNUMBER(SEARCH("Florida",RC[-2],1)),"Florida", IF(ISNUMBER(SEARCH("Texas",RC[-2],1)),"Texas", IF(ISNUMBER(SEARCH("New Mexico",RC[-2],1)),"New Mexico", IF(ISNUMBER(SEARCH("Alaska",RC[-2],1)),"Alaska", IF(ISNUMBER(SEARCH("New Jersey",RC[-2],1)),"New Jersey", ""))))))
'
Public Function CreateFromulaBlock(ByVal States As Variant) As String
    Dim FormulaString As String
    
    Dim State As Variant
    For Each State In States
        FormulaString = FormulaString & "IF(ISNUMBER(SEARCH(""" & State & """,RC[-2],1)),""" & State & """, "
    Next State
    
    CreateFromulaBlock = FormulaString & """""" & String(UBound(States) + 1, ")")
End Function

That is working, thank you. May I ask since I followed your syntax I need to add some data but I am receiving an error "Too many line continuations". Why is that? — Vangie, Sep 15 '21 at 10:18
Because there is a limit in VBA how many lines can be combined by ` _`. Make some function that can generate the formula, so you just need to add some states to the list. See my edit. — Pᴇʜ, Sep 15 '21 at 11:40

Syntax Error trying to write a formula into a cell with VBA

1 Answers1