Excel VBA Compile Time/ Syntax Error with a long formula

[alexk4444]

Hello,

I am trying to use a macro to remove middle initial (if it exists) from a list of names in column B. This formula is to be typed into cell D2. My formula works when I type it into excel, but not when I record it as a macro and run it. There is a compile time/syntax error. The code is below.
Thanks for any assistance.

Range("D2").Select ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-2])-LEN(SUBSTITUTE(RC[-2],"" "",""""))=1,RC[-2],IF(AND(LEN(RC[-2])-LEN(SUBSTITUTE(RC[-2],"" "",""""))=2,_"
OR(RIGHT(RC[-2],2)=""JR"",RIGHT(RC[-2],2)=""SR"",RIGHT(RC[-2],3)=""III"",RIGHT(RC[-2],3)="" IV"",RIGHT(RC[-2],3)="" II"",_
RIGHT(RC[-2],2)="" V"",RIGHT(RC[-2],2)=""VI"")),RC[-2],(TRIM(LEFT(RC[-2],FIND("" "",LOWER(RC[-2]),1)))&"" ""&TRIM(MID(RC[-2],FIND(""& _
"ER(RC[-2]),FIND("" "",LOWER(RC[-2]),1)+1)+1,LEN(RC[-2])-FIND("" "",LOWER(RC[-2]),1)+1)))))"

 

[Jerry Sullivan]

Hi Alex,

The main cause of your error is that in breaking apart the long string of your formula, you need to follow this syntax...

Start with:

"=My Super Long String"

At each line break, add pair of quotes split by & _ then linefeed:

"=My Super " & _
"Long " & _
"String"

Notice your first line break ends in this...
))=2,_"

You'll also need to split this into two statements

Range("D2").Select 
ActiveCell.FormulaR1C1= _

Note that you don't need to Select the cell to enter a formula in it. This is better...

Range("D2").FormulaR1C1 = _

There might be simpler methods to achieve your objective than entering this long formula, but I've limited my suggestions to try to help you get this approach to work without an error.