If/And Greater than or Less Than

[Guest]

Hi and thanks again for any suggestions forthcoming,

Deadlines are looming and I was hoping someone could help with a formula
that goes something like:

If A1=XXX, And A2 >=1, And A2<=10, do nothing. If A1= YYY, And A2>=5, And
A2<=10, do nothing. (etc) If False, Message Box. There are 4 sets of
conditions to test for.

Thanks,

 

[Guest]

Select Case True
case Range("A1").Value="XXX" And _
Range("A2").Value >=1 And Range("A2").VAlue <=10
' do whatever
case Range("A1").Value="YYY" And _
Range("A2").Value >=5 And Range("A2").VAlue <=10
' do whatever
Case . . .

Case . . .

Case Else

End Select

 

[Guest]

Tom,

Thanks for your response but I think I forgot to mention that I really am
very new to this and need something idiotproof!

The code I've entered (as per your post) is as follows but nothing is
happening:

Select Case True
Case Range("B10").Value = "5.5" And _
Range("C21").Value >= 4 And Range("C21").Value <= 7.1
msg = "Indoor/Outdoor Unit Selection OK"
Case Range("B10").Value = "6.7" And _
Range("C21").Value >= 4 And Range("C21").Value <= 8.8
msg = "Indoor/Outdoor Unit Selection OK"
Case Range("B10").Value = "6.8" And _
Range("C21").Value >= 4 And Range("C21").Value <= 10.6
msg = "Indoor/Outdoor Unit Selection OK"
Case Range("B10").Value = "8" And _
Range("C21").Value >= 7.8 And Range("C21").Value <= 14.3
msg = "Indoor/Outdoor Unit Selection OK"
Case Else
msg = "Indoor/Outdoor Units Selection Incorrect. Please Reselect"

End Select

If you can see where I'm going wrong and point me in the right direction,
I'll be most grateful.

Thanks

 

[Guest]

Still struggling a bit, although the following code is working it is not
activating automatically. Any hints much appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = ("B10") Then
Select Case True
Dim Msg As String
Select Case Range("B10").Value = "5.5" And _
Range("C21").Value >= 4 And Range("C21").Value <= 7.1
Case True
Msg = "OK"
Case Else
Select Case Range("B10").Value = "6.7" And _
Range("C21").Value >= 4 And Range("C21").Value <= 8.8
Case True
Msg = "OK"
Case Else
Select Case Range("B10").Value = "6.8" And _
Range("C21").Value >= 4 And Range("C21").Value <= 10.6
Case True
Msg = "OK"
Case Else
Select Case Range("B10").Value = "8" And _
Range("C21").Value >= 7.8 And Range("C21").Value <= 14.3
Case True
Msg = "OK"
Case Else
Msg = "Not OK, Please Re-select"
End Select
End Select
End Select
End Select
End If
MsgBox "Indoor/Outdoor Unit Selection " & "" & Msg
End Sub

Thanks.

(I suspect it could also be a lot more concise than I have it).

 

[Peter T]

Just at a glance of your code it seems you various have pairs of conditions
and probably simpler to use nested If..Elseif..End If constructs, eg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg, vB10, vC21

If Target.Address = ("B10") Then
vB10 = Range("B10").Value
vC21 = Range("C21").Value

If vB10 = 5.5 And _
vC21 >= 4 And vC21 <= 7.1 Then
Msg = "OK"
Else
If vB10 = 6.7 And _
vC21 >= 4 And vC21 <= 8.8 Then

Msg = "OK"
Else
If vB10 = 6.8 And _
vC21 >= 4 And vC21 <= 10.6 Then

Msg = "OK"
Else
If vB10 = 8 And _
vC21 >= 7.8 And vC21 <= 14.3 Then
Msg = "OK"
Else
Msg = "Not OK, Please Re-select"
End If
End If
End If
End If
'msgbox moved into the first If test
MsgBox "Indoor/Outdoor Unit Selection " & Msg
End If
'MsgBox "Indoor/Outdoor Unit Selection " & "" & Msg
End Sub

I have no idea if this is want you want but I suspect you'll find it easier
to debug and correct to your needs.

Regards,
Peter T

 

[Guest]

Hi Peter,

Thanks for the reply. I've tried the code which seems to be exactly what I
want, but again, it's not activating automatically. I'm sure the problem
must be really basic (and obvious!) and was wondering if it could be due to
the fact that the contents of B10 and C21 are as a result of a calculation?

 

[Peter T]

The Worksheet_Change event fires when the contents of a cell change, BUT the
new result of a formula is NOT a contents change.

So to trap results of your formulas changing you actually need to trap cells
whose constants end up changing the formula results (possibly after updating
some other formula), eg

A10 = sum(A1:A9)
B10 = A10 * 2

to trap B10's formula result changing you need to trap as a minimum A1:A9,
assuming A1:A9 are all constants.

in the change event

If not Intersect(Target, Range("A1:A9") is nothing then
If Range("B10") = 1 then etc

For your purposes you might not even need to start by testing the Target
range at all, but it helps speed up the routine by aborting early if the
changing cell(s) are not in the Target range, and hence do not impact on the
formula cell you are interested in.

Regards,
Peter T

 

[Guest]

Thanks for that Peter, I thought it might be the case.

Will work on it tomorrow ......fingers crossed!