Solved: Formula too long for vba?

[kroz]

Hey guys,

I'm trying to record a macro with a formula in a workbook, formula that is quite long.
And by record i mean that I've already written the formula in my cell, then pressed Macros record and then clicked in and out of the cell.. the usual stuff.
Only this time EXCEL says "Unable to record".

The formula is this:

=IF(AND($E10<>"",OR($E10>$T$6,E10<$S$6)),IF(ISNA($D10),IF(AND($B10<>"", _
ISNUMBER($B10)),"Error in PID/Error in property ID/Error in Material","Error in PID/Error in property ID"), _
IF(AND($B10<>"",ISNUMBER($B10)),"Error in PID/Error in Material","Error in PID")), _
IF(ISNA($D10),IF(AND($B10<>"",ISNUMBER($B10)),"Error in property ID/Error in Material", _
"Error in Property ID"),IF(AND($B10<>"",ISNUMBER($B10)),"Error in Material","OK")))

Is it too long for VBA?

[GTO]

I am not clear on what you are wanting to do, but if you are looking to have vba plunk the formula into a cell, you need to double up the quotes.

Mark

[kroz]

I'm trying to use Record Macro to convert the formula to formulaR1C1.

[Bob Phillips]

ActiveCell.FormulaR1C1 = _
"=IF(AND(R[9]C5<>"""",OR(R[9]C5>R6C20,R[9]C[-8]<R6C19)),IF(ISNA(R[9]C4)," & vbLf & _
" IF(AND(R[9]C2<>"""",ISNUMBER(R[9]C2)),""Error in PID/Error in property ID/Error in Material"",""Error in PID/Error in property ID"")," & vbLf & _
" IF(AND(R[9]C2<>"""",ISNUMBER(R[9]C2)),""Error in PID/Error in Material"",""Error in PID""))," & vbLf & _
" IF(ISNA(R[9]C4),IF(AND(R[9]C2<>"""",ISNUMBER(R[9]C2)),""Error in property ID/Error in Material"",""Error in Property ID"")," & vbLf & _
" IF(AND(R[9]C2<>"""",ISNUMBER(R[9]C2)),""Error in Material"",""OK"")))"

[kroz]

Thanx xld, i had no real problem with converting the formula by hand, i was just surprised that EXCEL doesn't want to convert it automatically in Record Macro mode.

Thanx for the conversion

[Bob Phillips]

I didn't do it by hand, I recorded it!

[kroz]

must be my excel then..