Using Select Case Statements

[Bob]

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

 

[steve]

try this


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

 

[Bob]

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

Bob

 

[Bob]

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

Bob