Can't apply formula to cells using VBA

usedetx

New Member
Joined
Jan 11, 2008
Messages
19
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

Code:
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?

:confused:
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You need to double up on the internal "
"=LEFT(A2,FIND(""."",A2&""."")-1)"
 
Upvote 0
Apply double quotes inside a formula that is surrounded by quotes, ie.

Range("B2:B" & LastRowColumnA).Formula = "=LEFT(A2,FIND(""."",A2&""."")-1)"
 
Upvote 0
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:
Code:
Range("B2:B" & LastRowColumnA).FormulaR1C1 = "=LEFT(RC[-1],FIND(""."",RC[-1]&""."")-1)"
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top