IF argument limit - how convert formula to VBA?

[numbermonkey]

I have hit the limit with IF statements in a formula I have i
questionnaire I creted in Excel 2010. How would I create this formul
in VBA?

"TRUE" is returned in those cells where a "Yes" radio button i
selected.
I would like the macro to run when a button is selected, and for it t
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,C14=TRUE,C15=TRUE,C16=TRUE,C17=TRUE,C18=TRUE,C19=TRUE),"B
Owned (Circuit)",

IF(AND(C6=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE,C15=TRUE,C17=TRUE,C18=TRUE,C19),"B
Owned (VPN)",

IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C18=TRUE,C19=TRUE),"Share
VPN (IPSec)",
IF(AND(C7=TRUE,C8=TRUE,C12=TRUE,C13=TRUE,C19=TRUE),"Shared VP
(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),"Share
(Dedicated Circuit)",

IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C12=TRUE,C13=TRUE,C14=TRUE,C17=TRUE,C18=TRUE,C19=TRUE),"HN
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),"HN
Owned (MPLS)","No Solution"))))))

 

[witek]

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,C14=TRUE,C15=TRUE,C16=TRUE,C17=TRUE,C18=TRUE,C19=TRUE),"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,C18=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,C11=TRUE,C12=TRUE,C13=TRUE,C14=TRUE,C17=TRUE,C18=TRUE,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,C19=TRUE)

IF(AND(cell1, cell2),"BP Owned (Circuit)",


That will be much faster than VBA code.

 

[Harald Staff]

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