Can't apply formula to cells using VBA

[usedetx]

Hello Friends of Excel,

I found this great formula on this forum that executes exactly what I need. https://www.mrexcel.com/forum/excel...haracters-cell-before-specific-character.html

However, I try to appy this formula to a range of cells using VBA, I get a Compile Error: Syntax Error

Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & LastRowColumnA).Formula = "=LEFT(A2,FIND(".",A2&".")-1)"

My third line of code is highlighted red. I think this is because VBA is seeing the starting " and thinks it ends with the next ", when really it doesn't, that is just part of the formula.

Is this correct? If so, if there another formula that could be used to retrieve a variable number of characters in a cell before a specific character? OR is there a better way to apply this formula to a used range that matches the used range in Column A?

 

[Fluff]

You need to double up on the internal "
"=LEFT(A2,FIND(""."",A2&""."")-1)"

 

[alansidman]

Apply double quotes inside a formula that is surrounded by quotes, ie.

Range("B2:B" & LastRowColumnA).Formula = "=LEFT(A2,FIND(""."",A2&""."")-1)"

 

[Joe4]

Yes, literal double-quotes get confused with text qualifier double-quotes.

When this happens, I find the easiest thing to do is turn on the Macro Recorder and record myself entering the formula manually, then inspecting the code it created to see how it need to look.
And that is:

Range("B2:B" & LastRowColumnA).FormulaR1C1 = "=LEFT(RC[-1],FIND(""."",RC[-1]&""."")-1)"

 

[usedetx]

Thank you! Thank You! I appreciate the education and the end of my headache.

 

[Fluff]

Glad we could help & thanks for the feedback