CODEWITHSUNDEEP
excelvba
rob
rob

Reputation: 317

Expected End of Statement Error When Inserting Formula into Excel with VBA

I'm getting Expected End of Statement Error with the code below. The debugger is highlighting the full stop/period but that is part of the formula.

Can anyone see the issue?

Sub ParseSurnames()
    Dim lastRow As Long
   
    'Where is last row of data?
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    'Apply formula
    Range("C1:C" & lastRow).Formula = "=IF(A1="","",LEFT(A1,FIND(".",A1)-3))"

End Sub

Upvotes: 0

Views: 350

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try changing the code line applying the formula in:

Range("C1:C" & lastRow).Formula = "=IF(A1="""","""",LEFT(A1,FIND(""."",A1)-3))"

In a string containing ", the double quote must be escaped, using another one. I mean """" for "" and "" for "...

In order to check such strings, please try the next code line:

Debug.Print "=IF(A1="""","""",LEFT(A1,FIND(""."",A1)-3))"

Does it return in Immediate Window (Ctrl + G, being in VBE) the formula as you need? If yes, this is the way...

Upvotes: 2

Related Questions