# Thread: Limits to Formulae (VBA/MS Excel/97)

1. ## Limits to Formulae (VBA/MS Excel/97)

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...

<pre>=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)))))
</pre>

But this is all that showed up...

<pre>"=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'!"</pre>

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?

2. ## Re: Limits to Formulae (VBA/MS Excel/97)

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

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

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

Leigh

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•