Limits to Formulae (VBA/MS Excel/97)

Topic

New Reply

To create the code for a complex formula I usually record a macro of that formula as it is copied and then pasted in the same cell. This has the advantage of less errors by hand writing the code that represents a formula in VBA.

Until now that is.

The following formula is what I wanted to record…

=IF(OR('WSheet'!A10="",'Results Sheet'!N10="ERROR"),"", _
IF('WSheet'!$D$6=1,VLOOKUP('WSheet'!A10,'[Refs.xls]Area'!$B$186:$I$216,6,FALSE), _
IF('WSheet'!$D$6=2,VLOOKUP('WSheet'!A10,'[Refs.xls]Area'!$B$222:$I$255,6,FALSE), _
IF('WSheet'!$D$6=3,VLOOKUP('WSheet'!A10,'[Refs.xls]Area'!$B$261:$I$280,6,FALSE)))))

But this is all that showed up…

"=OR('WSheet'!R[-123]C[-10]="""",'Results Sheet'!R[-123]C[3]=""ERROR"")b"""" _
'WSheet'!R6C4=1 VLOOKUP('WSheet'!R[-123]C[-10],'[Refs.xls]Area'!R186C2:R216C9,6,FALSE) _
?'WSheet'!"

Note the letter ‘b’ and the ‘?’. I have used the method successfully for other formulae, albeit, not as long and they have been OK.

Any suggestions, Leigh?

Reply | Quote

Replies

If anyone has an interest in this problem, the solution is simply to use the following reference when building the formula.

ActiveCell.Formula = "=IF(OR('WSheet'!A10="",'Results Sheet'!N10="ERROR"),"", ...." 

This keeps the formula style that it was created in, and not FormulaR1C1 style.

Leigh

Reply | Quote