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")))))))

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.

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