VBA Excel formula compile error - expected end of statement

Question

I am getting a this error:

Compile error: Expected: End of Statement

While trying to use an Excel formula in a VBA statement in the code below.

The error occurs on "FIND("for ","...

For i = 2 To lastRow
    aa = ThisWorkbook.Sheets(3).Cells(i, "A").Text                  
    ThisWorkbook.Sheets(3).Cells(i, "E").Formula = "=MID(aa,FIND("for ",aa)+4,FIND(" ",aa,FIND("for ",aa)+4)-(FIND("for ",aa)+4))"
Next i

Possible duplicate of [Expected End of Statement in formula with quotes](http://stackoverflow.com/questions/24173238/expected-end-of-statement-in-formula-with-quotes) — Robin Mackenzie, Mar 15 '17 at 12:04
Try this `"=MID(aa,FIND(""for "",aa)+4,FIND("" "",aa,FIND(""for "",aa)+4)-(FIND(""for "",aa)+4))"` — Robin Mackenzie, Mar 15 '17 at 12:04
I'll add as a comment as the duplicate link answers the question. Use `.FormulaR1C1 = "=MID(RC1,FIND(""for "",RC1)+4,FIND("" "",RC1,find(""for "",RC1)+4)-(FIND(""for "",RC1)+4))"`. `aa` is the text as displayed in column A. `RC1` references column 1 (A) of the row the formula is placed in. — Darren Bartrup-Cook, Mar 15 '17 at 12:11
@RobinMackenzie but `aa` is a variable, you need to extract it from your formula by adding a `" & aa & "` — Shai Rado, Mar 15 '17 at 12:16
@ShaiRado - yes you're right - didn't read question properly:( I thought it was a named range. — Robin Mackenzie, Mar 15 '17 at 12:18

score 0 · Answer 1 · answered Mar 15 '17 at 12:10

It's a little long:

You need to add the " inside your formula, I like to use the Chr(34) to add it.

So first, your aa is a varaible that changes every time you advance i in the loop, so inside the formula you need to break the constant part and add " & aa & " every time you use it. But, you need to add the " to aa, that's why I modifed your aa line to:

aa = Chr(34) & ThisWorkbook.Sheets(3).Cells(i, "A").Text & Chr(34)

Second, I added a String variable named Myfor, and set the value to it Myfor = Chr(34) & "for " & Chr(34), just to try to "shorten" the formula a little.

Code

Dim aa          As String
Dim Myfor       As String

Myfor = Chr(34) & "for " & Chr(34)
For i = 2 To lastRow
    aa = Chr(34) & ThisWorkbook.Sheets(3).Cells(i, "A").Text & Chr(34)
    ThisWorkbook.Sheets(3).Cells(i, "E").Formula = "=MID(" & aa & ",FIND(" & Myfor & "," & aa & ")+4, FIND("" ""," & aa & ",FIND(" & Myfor & "," & aa & ")+4)-(FIND(" & Myfor & "," & aa & ")+4))"
Next i

VBA Excel formula compile error - expected end of statement

1 Answers1