If Statements with 4 tests (2 tests include a range of numbers)

Posted by Neil on January 02, 2002 2:02 PM

I am having problems trying to figure out a nested If statement. Here is what I need to be returned in a cell.

>5:54 return a 4 in the cell
6:55 to 7:00 return a 3 in the cell
7:01 to 7:10 return a 2 in the cell
&LT;7:11 return a 1 in the cell

Posted by Jacob on January 02, 2002 3:00 PM

Forget about nested ifs. They get tooooo messy. Cases do the same thing and are easier to visualise.

Sub MyMacro()
Dim MyValue
MyValue = the number you want or a range to where the number is.

'Your Code Here

Select Case MyValue

Case is &LT;5:54
range("A1").formular1c1=4
Case 6:55 to 7:00
range("A1").formular1c1=3
case 7:01 to 7:10
range("A1").formular1c1=2
case is > 7:11
range("A1").formular1c1=1
case else
msgbox("I dont know what to do."),vbcritical,"Error"

'Your Code Here
End Sub

HTH
Jacob

Posted by IML on January 02, 2002 3:35 PM

I assumed these times were minutes and seconds. I further assumed anything 5:54 and below should be n/a, and antything above 7:11 to get a 1.

assuming your time A1,
=VLOOKUP(A1*86400,{0,"n/a";354.01,4;415,3;421,2;431,1},2)

good luck