Long Excel Formula in VBA gives error

[Steve Mackay]

Hi all...using Excel 2002. I am trying to record a macro to put this
formula in a cell, but get an error "unable to record" when I try to
record it:

=IF(OR('CBA Template'!$L$275=1,'CBA
Template'!$L$279=1),"A",IF(AND(OR('CBA Template'!$F$230="<1",'CBA
Template'!$F$230=1),'CBA Template'!$J$37>0,'CBA
Template'!$F$228>0),"B",IF(AND('CBA Template'!$F$228>0,'CBA
Template'!$J$37>0,'CBA Template'!$L$281=1),"C",IF(OR(AND('CBA
Template'!$F$228>0,'CBA Template'!$L$281=2),AND('CBA
Template'!$F$228>0,'CBA Template'!$J$37<0,'CBA
Template'!$L$281=1)),"D","E"))))

It is pretty long, but the gist of it is that if a certain set of
conditions is met, then the formula returns "A", if another set is met,
"B", etc through "E". The formula works properly, I am just not able
to record it, and I am not sure why. Any ideas?

Is there a way to hard code it without trying to record?

Thanks,
Steve Mackay

 

[STEVE BELL]

Try putting something like this in your code:

Workbooks("MyBook").Sheets("MySheet").Range("A1").Formula = _
=IF(OR('CBA Template'!$L$275=1,'CBA _
Template'!$L$279=1),""A"",IF(AND(OR('CBA
Template'!$F$230=""<1"",'CBA _
Template'!$F$230=1),'CBA Template'!$J$37>0,'CBA _
Template'!$F$228>0),""B"",IF(AND('CBA Template'!$F$228>0,'CBA _
Template'!$J$37>0,'CBA Template'!$L$281=1),""C"",IF(OR(AND('CBA _
Template'!$F$228>0,'CBA Template'!$L$281=2),AND('CBA _
Template'!$F$228>0,'CBA Template'!$J$37<0,'CBA _
Template'!$L$281=1)),""D"",""E""))))

If "MySheet" is the activesheet than you can use

Range("A1").Formula = "= ....."

Just remember to use double quotes ("" "") instead of single quotes (" ")
and be sure to use the line extender ( _ ) to get around wrapping text.

Than be careful of exceeding the 255 characters per cell.

 

[JE McGimpsey]

One way:

Range("J10").Formula = "=IF(OR('CBA Template'!$L$275=1," & _
"'CBA Template'!$L$279=1),""A"",IF(AND(OR(" & _
"'CBA Template'!$F$230=""<1"",'CBA Template'!$F$230=1)," & _
"'CBA Template'!$J$37>0,'CBA Template'!$F$228>0)," & _
"""B"",IF(AND('CBA Template'!$F$228>0," & _
"'CBA Template'!$J$37>0,'CBA Template'!$L$281=1)," & _
"""C"",IF(OR(AND('CBA Template'!$F$228>0," & _
"'CBA Template'!$L$281=2),AND('CBA Template'!$F$228>0," & _
"'CBA Template'!$J$37<0,'CBA Template'!$L$281=1))," & _
"""D"",""E""))))"

Note you need to double quotation marks inside a string, just as in XL.

 

[Steve Mackay]

Ah, I think that is the problem...I am exceeding 255 chars. When I try
to use the code you put, I still get an error. Application definded or
object defined error.

So, a cell can only have a formula <255 chars?

Thanks,
Steve

 

[JE McGimpsey]

No. It works fine for me using XL03 and XL04...

 

[Steve Mackay]

Sorry, I wasn't specific. the formula works fine in Excel, but if you
try to code it using VBA, then from what Steve Bell posted, it appears
you can't exceed 255 chars per cell. When I named the cells to trim
down the formula, then it worked. But when it was larger than 255, it
gave me errors.

Thanks both for the double quote comments. Obviously I am relatively
novice with macros...I didn't realize that's what I needed to do.

Thanks,
Steve

 

[Dave Peterson]

JE's code worked ok for me.

You may want to try it one more time.

 

[JE McGimpsey]

You were specific enough. When I use VBA to code the formula into the
cell, it works fine.

 

[Steve Mackay]

You are right...thank you very much. It did work when I tried it
again. Thanks for taking the time to post and follow up

Steve

 

[Tom Ogilvy]

Just to add one more voice.
JE's, code worked fine for me in xl97. Suggest you have a typo in your
longer formula. Normal formulas have a limit of 1024 characters, not 255.
If you use FormulaArray to enter an array formula, then that is limited to
255. Perhaps that is what Steve Bell was thinking of. But that is not a
limitation here.