Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF argument limit - how convert formula to VBA?
I have hit the limit with IF statements in a formula I have in questionnaire I creted in Excel 2010. How would I create this formula in VBA?
"TRUE" is returned in those cells where a "Yes" radio button is selected. I would like the macro to run when a button is selected, and for it to post the return (i.e., "BP Owned Circuit") in cell B6. Thanks! Here's the formula: = IF(AND(C3=TRUE,C4=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C 14=TRUE,C15=TRUE,C16=TRUE,C17=TRUE,C18=TRUE,C19=TR UE),"BP Owned (Circuit)", IF(AND(C6=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE, C15=TRUE,C17=TRUE,C18=TRUE,C19),"BP Owned (VPN)", IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C1 8=TRUE,C19=TRUE),"Shared VPN (IPSec)", IF(AND(C7=TRUE,C8=TRUE,C12=TRUE,C13=TRUE,C19=TRUE) ,"Shared VPN (SSL/Client)", IF(AND(C3=TRUE,C4=TRUE,C5=TRUE,C7=TRUE,C9=TRUE,C10 =TRUE,C11=TRUE,C12=TRUE,C13=TRUE,C16=TRUE,C17=TRUE ,C18=TRUE,C19=TRUE),"Shared (Dedicated Circuit)", IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C1 1=TRUE,C12=TRUE,C13=TRUE,C14=TRUE,C17=TRUE,C18=TRU E,C19=TRUE),"HNB Owned (VPN)", IF(AND(C3=TRUE,C4=TRUE,C5=TRUE,C6=TRUE,C7=TRUE,C9= TRUE,C10=TRUE,C11=TRUE,C12=TRUE,C13=TRUE,C14=TRUE, C16=TRUE,C17=TRUE,C18=TRUE,C19=TRUE),"HNB Owned (MPLS)","No Solution"))))))) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF argument limit - how convert formula to VBA?
numbermonkey wrote:
I have hit the limit with IF statements in a formula I have in questionnaire I creted in Excel 2010. How would I create this formula in VBA? "TRUE" is returned in those cells where a "Yes" radio button is selected. I would like the macro to run when a button is selected, and for it to post the return (i.e., "BP Owned Circuit") in cell B6. Thanks! Here's the formula: = IF(AND(C3=TRUE,C4=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C 14=TRUE,C15=TRUE,C16=TRUE,C17=TRUE,C18=TRUE,C19=TR UE),"BP Owned (Circuit)", IF(AND(C6=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE, C15=TRUE,C17=TRUE,C18=TRUE,C19),"BP Owned (VPN)", IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C1 8=TRUE,C19=TRUE),"Shared VPN (IPSec)", IF(AND(C7=TRUE,C8=TRUE,C12=TRUE,C13=TRUE,C19=TRUE) ,"Shared VPN (SSL/Client)", IF(AND(C3=TRUE,C4=TRUE,C5=TRUE,C7=TRUE,C9=TRUE,C10 =TRUE,C11=TRUE,C12=TRUE,C13=TRUE,C16=TRUE,C17=TRUE ,C18=TRUE,C19=TRUE),"Shared (Dedicated Circuit)", IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C1 1=TRUE,C12=TRUE,C13=TRUE,C14=TRUE,C17=TRUE,C18=TRU E,C19=TRUE),"HNB Owned (VPN)", IF(AND(C3=TRUE,C4=TRUE,C5=TRUE,C6=TRUE,C7=TRUE,C9= TRUE,C10=TRUE,C11=TRUE,C12=TRUE,C13=TRUE,C14=TRUE, C16=TRUE,C17=TRUE,C18=TRUE,C19=TRUE),"HNB Owned (MPLS)","No Solution"))))))) Can't you split formula? first one: cell 1 = AND(C3=TRUE,C4=TRUE,C9=TRUE,C10=TRUE,C11=TRUE) cell 2 = AND(C14=TRUE,C15=TRUE,C16=TRUE,C17=TRUE,C18=TRUE,C 19=TRUE) IF(AND(cell1, cell2),"BP Owned (Circuit)", That will be much faster than VBA code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF argument limit - how convert formula to VBA?
Hi
Here's an idea for you to play with: TRUE in a spreadsheet (but not in VBA) is 1 and FALSE is 0. Treat the series as a single binary number. Simplified =C3+2*C4 if 0, both are false if 1, C3 true and C4 false if 2, C3 false and C4 true if 3, both are true Best wishes Harald "numbermonkey" skrev i melding ... I have hit the limit with IF statements in a formula I have in questionnaire I creted in Excel 2010. How would I create this formula in VBA? "TRUE" is returned in those cells where a "Yes" radio button is selected. I would like the macro to run when a button is selected, and for it to post the return (i.e., "BP Owned Circuit") in cell B6. Thanks! Here's the formula: = IF(AND(C3=TRUE,C4=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C 14=TRUE,C15=TRUE,C16=TRUE,C17=TRUE,C18=TRUE,C19=TR UE),"BP Owned (Circuit)", IF(AND(C6=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE, C15=TRUE,C17=TRUE,C18=TRUE,C19),"BP Owned (VPN)", IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C1 8=TRUE,C19=TRUE),"Shared VPN (IPSec)", IF(AND(C7=TRUE,C8=TRUE,C12=TRUE,C13=TRUE,C19=TRUE) ,"Shared VPN (SSL/Client)", IF(AND(C3=TRUE,C4=TRUE,C5=TRUE,C7=TRUE,C9=TRUE,C10 =TRUE,C11=TRUE,C12=TRUE,C13=TRUE,C16=TRUE,C17=TRUE ,C18=TRUE,C19=TRUE),"Shared (Dedicated Circuit)", IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C1 1=TRUE,C12=TRUE,C13=TRUE,C14=TRUE,C17=TRUE,C18=TRU E,C19=TRUE),"HNB Owned (VPN)", IF(AND(C3=TRUE,C4=TRUE,C5=TRUE,C6=TRUE,C7=TRUE,C9= TRUE,C10=TRUE,C11=TRUE,C12=TRUE,C13=TRUE,C14=TRUE, C16=TRUE,C17=TRUE,C18=TRUE,C19=TRUE),"HNB Owned (MPLS)","No Solution"))))))) -- numbermonkey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workaround for HYPERLINK argument length limit | Excel Worksheet Functions | |||
How can I get around 30 argument limit of SUM function? | Excel Worksheet Functions | |||
Redesign for hitting limit argument in a UDF | Excel Programming | |||
Argument limit in Excel Function Wizard | Excel Programming | |||
Argument limit on user Function? | Excel Programming |