Excel VBA errors on megaformula 1

[Evil8]

I am trying to extract middle names in a vba macro in excel 2003, but the I keep getting an error.

ActiveCell.FormulaR1C1 = _
        "=IF(ISERR(MID(RC[+4],FIND(" [COLOR=red]",RC[+4])+1,IF(ISERR(FIND("[/color] ",RC[+4],FIND(" ",RC[+4])+1)),FIND(" ",RC[+4]),FIND(" ",RC[+4],FIND(" ",RC[+4])+1))-FIND(" ",RC[+4])-1)),"",MID(RC[+4],FIND(" ",RC[+4])+1,IF(ISERR(FIND(" ",RC[+4],FIND(" ",RC[+4])+1)),FIND(" ",RC[+4]),FIND(" ",RC[+4],FIND(" ",RC[+4])+1))-FIND(" ",RC[+4])-1))"

When working in the VBA Editor I get a Compile error: Expected: end of statement and the section that is red is highlighted in grey. I get a can't record error when I try to use the macro recorder as well.

Any help is appreciated.
Evil8

 

[PHV]

ActiveCell.FormulaR1C1 = _
"=IF(ISERR(MID(RC[+4],FIND("" "",RC[+4])+1,IF(ISERR(FIND("" "",RC[+4],FIND("" "",RC[+4])+1)),FIND("" "",RC[+4]),FIND("" "",RC[+4],FIND("" "",RC[+4])+1))-FIND("" "",RC[+4])-1)),"""",MID(RC[+4],FIND("" "",RC[+4])+1,IF(ISERR(FIND("" "",RC[+4],FIND("" "",RC[+4])+1)),FIND("" "",RC[+4]),FIND("" "",RC[+4],FIND("" "",RC[+4])+1))-FIND("" "",RC[+4])-1))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Evil8]

Yes! I got to get more sleep, I feel pretty stupid this morning for not remember that I need extra quotation marks.