Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > If/And Greater than or Less Than
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default If/And Greater than or Less Than
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,








Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default If/And Greater than or Less Than
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

--
Regards,
Tom Ogilvy


"Anniem" wrote:

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,








Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default If/And Greater than or Less Than
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



"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Anniem" wrote:

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,








Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default If/And Greater than or Less Than
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).

"Anniem" wrote:

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,








Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default If/And Greater than or Less Than
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


"Anniem" wrote in message
...
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).

"Anniem" wrote:

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,










Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default If/And Greater than or Less Than
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" wrote:

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


"Anniem" wrote in message
...
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).

"Anniem" wrote:

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,











Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default If/And Greater than or Less Than
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


"Anniem" wrote in message
...
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" wrote:

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


"Anniem" wrote in message
...
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).

"Anniem" wrote:

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,













Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11
Default If/And Greater than or Less Than
Thanks for that Peter, I thought it might be the case.

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


"Peter T" wrote:

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


"Anniem" wrote in message
...
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" wrote:

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


"Anniem" wrote in message
...
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).

"Anniem" wrote:

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,














Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
If greater than 5 Carolan Excel Worksheet Functions 4 May 28th 10 03:00 AM
If formula: if A1 is greater than B1 and A1 is greater than zero.. Jason Excel Discussion (Misc queries) 2 April 5th 10 06:11 PM
Greater than and less than Farmer Ted Excel Discussion (Misc queries) 4 January 12th 10 04:19 AM
CountIf Greater Than/Find Greater Than Sisilla[_2_] Excel Programming 12 October 6th 06 08:04 PM
if greater then 99 then 1 if greater then 199 then two N.R. Excel Worksheet Functions 2 June 23rd 05 06:14 PM


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement