Case Select Statement

[Frimousse]

Hi,

I need a case statement with multiple(well only 2) conditions being met.
Is there a way to do this. Here is what i've got:

[vba]Case Is <= Cells(13, i).Value, Is > Cells(12, i).Value[/vba]

The program will assume this is true if either the first OR the second equation is true.

I need this case to be selected if both (AND) conditions are met.

I tried replacing the comma with: AND, &, +. None of them works. Can this be done?

[lucas]

You need an IF/And statement and the operators need to follow the range....

can you post an example file?

[lucas]

Here's an example macro using case with and, etc:
[VBA]Sub Using_Case()
' Dimension the variable.
Dim x As Integer
' Place a value in x.
x = Int(Rnd * 100)
' Display the value of x.
MsgBox "The value of x is " & x & "."
' Start the Select Case structure.
Select Case x
' Test to see if x less than or equal to 10.
Case Is <= 10
' Display a message box.
MsgBox "X is <=10"
' Test to see if x less than or equal to 40 and greater than 10.
Case 11 To 40
MsgBox "X is <=40 and > 10"
' Test to see if x less than or equal to 70 and greater than 40.
Case 41 To 70
MsgBox "X is <=70 and > 40"
' Test to see if x less than or equal to 100 and greater than 70.
Case 71 To 100
MsgBox "X is <= 100 and > 70"
' If none of the above tests returned true.
Case Else
MsgBox "X does not fall within the range"
End Select
End Sub
[/VBA]

[Frimousse]

Hi Lucas,

Thanks for help but this won't work as my conditions are based on cells which contains formula and numbers will change on a regular basis.

I ended up building a messy if,elseif statement.

[mdmackillop]

You can refer to cells
[VBA]
Sub test()
Select Case Range("A1")
Case Is < Range("H5")
MsgBox "Small"
Case Range("H5") To Range("J5")
MsgBox "Medium"
Case Is > Range("J5")
MsgBox "Large"
End Select
End Sub

[/VBA]