Syntax Error for formula

[JoeRooney]

Hi ,

I am recording my macro to input a formula , it is amending the original formula to the below but when I try and run it , it creates a syntax error.

Anyone any ideas what is causing this issue.

Thanks,

ActiveCell.FormulaR1C1 = _
"=IF(AND(ISNUMBER(SEARCH(""Mr"",RC[1]))=TRUE,ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))=TRUE),""Staff ID"",IF(AND(ISNUMBER(SEARCH(""Ms"",RC[1]))=TRUE,ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))=TRUE),""Staff ID"",IF(AND(ISNUMBER(SEARCH(""Miss"",RC[1]))=TRUE,ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))=TRUE),""Staff ID"",IF(AND(ISNUMBER(SEARCH(""Mrs"",RC[1]))=TRUE,ISNUMBER(SEARCH(""IENDD""& _
"1]))=TRUE),""Staff ID"",IF(AND(ISNUMBER(SEARCH(""/"",RC[1]))=TRUE,ISNUMBER(SEARCH(""IENDD"",R[1]C[1]))=TRUE),""Date"",""n/a"")))))"

 

[bhandari]

if you use F9 button.it can help you to evaluate the selecting stretch by this way You can figure out the Syntax Error

 

[joeu2004]

There are actually two syntax errors: a VBA syntax error and an Excel syntax error. The latter would cause a runtime error.

The syntax corrections are in red below. I also cleaned up the recorded macro code, removing unnecessary elements. That is prudent to do; recorded macros often have extraneous code. Finally, I broke up the strings differently in order to improve reability.

ActiveCell.FormulaR1C1 = _
 "=IF(AND(ISNUMBER(SEARCH(""Mr"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""Ms"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""Miss"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""Mrs"",RC[1])),ISNUMBER(SEARCH(""IENDD""" & _
",R[2]C[1))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""/"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[1]C[1]))),""Date"",""n/a"")))))"

The above is formatted in order to highlight the VBA syntax error (missing close-quote in the 5th line).

The following is how I would actual format it:

ActiveCell.FormulaR1C1 = _
"=IF(AND(ISNUMBER(SEARCH(""Mr"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""Ms"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""Miss"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""Mrs"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))),""Staff ID""," & _
"IF(AND(ISNUMBER(SEARCH(""/"",RC[1])),ISNUMBER(SEARCH(""IENDD"",R[1]C[1]))),""Date"",""n/a"")))))"

The Excel IF expression could be rewritten more efficiently. ISNUMBER(SEARCH("IENDD",R[2]C[1])) needs to be done only once. But that's another story altogether.

 

[joeu2004]

PS....

The Excel IF expression could be rewritten more efficiently. ISNUMBER(SEARCH("IENDD",R[2]C[1])) needs to be done only once. But that's another story altogether.

Okay, I'll share....

ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""IENDD"",R[2]C[1]))=FALSE,""n/a""," & _
"IF(ISNUMBER(SEARCH(""Mr"",RC[1])),""Staff ID""," & _
"IF(ISNUMBER(SEARCH(""Ms"",RC[1])),""Staff ID""," & _
"IF(ISNUMBER(SEARCH(""Miss"",RC[1])),""Staff ID""," & _
"IF(ISNUMBER(SEARCH(""Mrs"",RC[1])),""Staff ID""," & _
"IF(ISNUMBER(SEARCH(""/"",RC[1])),""Date"",""n/a""))))))"

 

[JoeRooney]

Thanks so much working perfectly