Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 147
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Workaround for HYPERLINK argument length limit Dave Booker Excel Worksheet Functions 5 April 4th 23 02:22 PM
How can I get around 30 argument limit of SUM function? Randy Excel Worksheet Functions 4 May 9th 07 09:05 PM
Redesign for hitting limit argument in a UDF Peter M Excel Programming 18 February 8th 05 02:09 AM
Argument limit in Excel Function Wizard [email protected] Excel Programming 1 February 5th 05 04:16 AM
Argument limit on user Function? Peter M Excel Programming 16 February 4th 05 08:47 PM


All times are GMT +1. The time now is 12:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"