Using Select Case Statements
 

With Excel 2002

I have a spreadsheet that makes extensive use of nested IF functions in
formulas. The spreadsheet has 15 cells for possible user input and 34 cells
with formulas that calculate results based on the 15 input cells. Eight of
the input cells do not require any input. Of the remaining 7 input cells,
users have the input options listed below. Unfortunately a recent worksheet
modification request has pushed the length of the longest formula beyond
Excel's limit for the number of characters in a formula and I'm
investigating a VBA solution.

My VBA experience is predominantly in Word, not Excel. I'm thinking a VBA
solution will involve one or more Select Case statements to identify which
input option was used and specify the appropriate formulas for the 34 cells
requiring calculations. I imagine I'll need to identify every possible case
that each of the options below is capable of producing, plus the cases that
result if users input data in the 8 non-required input cells.

It occurs to me that an alternative solution might be to continue using the
formulas in the worksheet that work, and use a Select Case statement only
for the new options required by the new worksheet modification request. I've
never used a Select Case statement in a macro and I'm looking for any advice
or related examples that might shed some light on how to proceed. Thanks.

Option 1
Enter a number in A5, B5, or C5
And
Enter a number in E5 or E6

i.e., A5, E5 = True

Option 2
Same as Case 1
And
Enter a number in G5

Option 3
Enter a number in A5, B5, or C5
And
Enter a number in I5

Option 4
Same as Case 3
And
Enter a number in G5

Option 5
Enter a number in E5 or E6
And
Enter a number in I5

Option 6
Same as Case 5
And
Enter a number in G5

Using Select Case Statements
 

try this


http://www.cpearson.com/excel/nested.htm

-----Original Message-----
With Excel 2002

I have a spreadsheet that makes extensive use of nested
IF functions in
formulas. The spreadsheet has 15 cells for possible user
input and 34 cells
with formulas that calculate results based on the 15
input cells. Eight of
the input cells do not require any input. Of the
remaining 7 input cells,
users have the input options listed below. Unfortunately
a recent worksheet
modification request has pushed the length of the
longest formula beyond
Excel's limit for the number of characters in a formula
and I'm
investigating a VBA solution.

My VBA experience is predominantly in Word, not Excel.
I'm thinking a VBA
solution will involve one or more Select Case statements
to identify which
input option was used and specify the appropriate
formulas for the 34 cells
requiring calculations. I imagine I'll need to identify
every possible case
that each of the options below is capable of producing,
plus the cases that
result if users input data in the 8 non-required input
cells.

It occurs to me that an alternative solution might be to
continue using the
formulas in the worksheet that work, and use a Select
Case statement only
for the new options required by the new worksheet
modification request. I've
never used a Select Case statement in a macro and I'm
looking for any advice
or related examples that might shed some light on how to
proceed. Thanks.

Option 1
Enter a number in A5, B5, or C5
And
Enter a number in E5 or E6

i.e., A5, E5 = True

Option 2
Same as Case 1
And
Enter a number in G5

Option 3
Enter a number in A5, B5, or C5
And
Enter a number in I5

Option 4
Same as Case 3
And
Enter a number in G5

Option 5
Enter a number in E5 or E6
And
Enter a number in I5

Option 6
Same as Case 5
And
Enter a number in G5


.

Using Select Case Statements
 

Thanks, Steve. I'll give it a try. It looks promising.

Bob

"steve" wrote in message
...
try this


http://www.cpearson.com/excel/nested.htm

-----Original Message-----
With Excel 2002

I have a spreadsheet that makes extensive use of nested
IF functions in
formulas. The spreadsheet has 15 cells for possible user
input and 34 cells
with formulas that calculate results based on the 15
input cells. Eight of
the input cells do not require any input. Of the
remaining 7 input cells,
users have the input options listed below. Unfortunately
a recent worksheet
modification request has pushed the length of the
longest formula beyond
Excel's limit for the number of characters in a formula
and I'm
investigating a VBA solution.

My VBA experience is predominantly in Word, not Excel.
I'm thinking a VBA
solution will involve one or more Select Case statements
to identify which
input option was used and specify the appropriate
formulas for the 34 cells
requiring calculations. I imagine I'll need to identify
every possible case
that each of the options below is capable of producing,
plus the cases that
result if users input data in the 8 non-required input
cells.

It occurs to me that an alternative solution might be to
continue using the
formulas in the worksheet that work, and use a Select
Case statement only
for the new options required by the new worksheet
modification request. I've
never used a Select Case statement in a macro and I'm
looking for any advice
or related examples that might shed some light on how to
proceed. Thanks.

Option 1
Enter a number in A5, B5, or C5
And
Enter a number in E5 or E6

i.e., A5, E5 = True

Option 2
Same as Case 1
And
Enter a number in G5

Option 3
Enter a number in A5, B5, or C5
And
Enter a number in I5

Option 4
Same as Case 3
And
Enter a number in G5

Option 5
Enter a number in E5 or E6
And
Enter a number in I5

Option 6
Same as Case 5
And
Enter a number in G5


.

Using Select Case Statements
 

You're the best, Steve. Your advice has opened up a new world of
possibilities. Thanks.

Bob

"steve" wrote in message
...
try this


http://www.cpearson.com/excel/nested.htm

-----Original Message-----
With Excel 2002

I have a spreadsheet that makes extensive use of nested
IF functions in
formulas. The spreadsheet has 15 cells for possible user
input and 34 cells
with formulas that calculate results based on the 15
input cells. Eight of
the input cells do not require any input. Of the
remaining 7 input cells,
users have the input options listed below. Unfortunately
a recent worksheet
modification request has pushed the length of the
longest formula beyond
Excel's limit for the number of characters in a formula
and I'm
investigating a VBA solution.

My VBA experience is predominantly in Word, not Excel.
I'm thinking a VBA
solution will involve one or more Select Case statements
to identify which
input option was used and specify the appropriate
formulas for the 34 cells
requiring calculations. I imagine I'll need to identify
every possible case
that each of the options below is capable of producing,
plus the cases that
result if users input data in the 8 non-required input
cells.

It occurs to me that an alternative solution might be to
continue using the
formulas in the worksheet that work, and use a Select
Case statement only
for the new options required by the new worksheet
modification request. I've
never used a Select Case statement in a macro and I'm
looking for any advice
or related examples that might shed some light on how to
proceed. Thanks.

Option 1
Enter a number in A5, B5, or C5
And
Enter a number in E5 or E6

i.e., A5, E5 = True

Option 2
Same as Case 1
And
Enter a number in G5

Option 3
Enter a number in A5, B5, or C5
And
Enter a number in I5

Option 4
Same as Case 3
And
Enter a number in G5

Option 5
Enter a number in E5 or E6
And
Enter a number in I5

Option 6
Same as Case 5
And
Enter a number in G5


.