Case Statements in Excel 2003

[Alex]

Stupid question, but is it possible to do a Case statement in Excel
2003? I can do an IF with no problem, but I need to test a field to
see if it meets about a dozen criteria, and it freaks out if I nest
more than 5 IF statements.

I've looked this up and found a few folks doing this in VB Script, but
given I've never done VBS in Excel, I don't even know where to begin
on that front if that's the only way.

Thanks in advance for any suggestions --

Alex

 

[Gary''s Student]

Sub routine()
v = ActiveCell.Value
Select Case v
Case "dog"
MsgBox ("a mammal")
Case "rose"
MsgBox ("a flower")
Case "guppy"
MsgBox ("a fish")
End Select
End Sub

 

[Alex]

Sub routine()
v = ActiveCell.Value
Select Case v
Case "dog"
    MsgBox ("a mammal")
Case "rose"
    MsgBox ("a flower")
Case "guppy"
    MsgBox ("a fish")
End Select
End Sub

Hi Gary,

Thanks for the reply, but this doesn't help much. Where would I plug
this or similar code into a spreadsheet to execute?

Thanks again - Alex

 

[Dave Peterson]

Maybe you could put your list of criteria in a column in a different worksheet
and whatever you want associated with each value in the adjacent cell. I'd use
A1:B12 (about).

Then I could use =vlookup() to retrieve that associated value.

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html (for =vlookup())