Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •