Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
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$370,'CBA Template'!$F$2280),"B",IF(AND('CBA Template'!$F$2280,'CBA Template'!$J$370,'CBA Template'!$L$281=1),"C",IF(OR(AND('CBA Template'!$F$2280,'CBA Template'!$L$281=2),AND('CBA Template'!$F$2280,'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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
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$370,'CBA _ Template'!$F$2280),""B"",IF(AND('CBA Template'!$F$2280,'CBA _ Template'!$J$370,'CBA Template'!$L$281=1),""C"",IF(OR(AND('CBA _ Template'!$F$2280,'CBA Template'!$L$281=2),AND('CBA _ Template'!$F$2280,'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. -- rand451 "Steve Mackay" wrote in message oups.com... 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$370,'CBA Template'!$F$2280),"B",IF(AND('CBA Template'!$F$2280,'CBA Template'!$J$370,'CBA Template'!$L$281=1),"C",IF(OR(AND('CBA Template'!$F$2280,'CBA Template'!$L$281=2),AND('CBA Template'!$F$2280,'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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
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$370,'CBA Template'!$F$2280)," & _ """B"",IF(AND('CBA Template'!$F$2280," & _ "'CBA Template'!$J$370,'CBA Template'!$L$281=1)," & _ """C"",IF(OR(AND('CBA Template'!$F$2280," & _ "'CBA Template'!$L$281=2),AND('CBA Template'!$F$2280," & _ "'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. In article .com, "Steve Mackay" wrote: 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$370,'CBA Template'!$F$2280),"B",IF(AND('CBA Template'!$F$2280,'CBA Template'!$J$370,'CBA Template'!$L$281=1),"C",IF(OR(AND('CBA Template'!$F$2280,'CBA Template'!$L$281=2),AND('CBA Template'!$F$2280,'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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
No. It works fine for me using XL03 and XL04...
In article .com, "Steve Mackay" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
JE's code worked ok for me.
You may want to try it one more time. Steve Mackay wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
You were specific enough. When I use VBA to code the formula into the
cell, it works fine. In article .com, "Steve Mackay" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Long Excel Formula in VBA gives error
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. -- Regards, Tom Ogilvy "Steve Mackay" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL ERROR - series formula is too long | Excel Worksheet Functions | |||
formula too long ERROR | Excel Worksheet Functions | |||
formula too long error? | Excel Worksheet Functions | |||
formula is too long error | Excel Worksheet Functions | |||
error: Formula is to long in Excel - How can I fix this? | Excel Discussion (Misc queries) |