Between to values

[Guest]

Hi!
I have a VB formula that look like this.

TemperaturInput=FrmSeawaterParameter25CrSDSS.TxtTemperature.Value

If TemperaturInput < 10 Then
PoFResult = 5
ElseIf TemperaturInput > 10 Or TemperaturInput < 15 Then
PoFResult = 4
ElseIf TemperaturInput > 15 Or TemperaturInput < 20 Then
PoFResult = 3
ElseIf TemperaturInput > 20 Or TemperaturInput < 25 Then
PoFResult = 2
ElseIf TemperaturInput > 25 Then
PoFResult = 1
End If

My problem is that this is not correct, if a set TemperaturInput=17 on this
i will get
PoFResult =4 and that is NOT correct!

What is wrong?

 

[Bob Phillips]

You want AND

If TemperaturInput < 10 Then
PoFResult = 5
ElseIf TemperaturInput > 10 And TemperaturInput < 15 Then
PoFResult = 4
ElseIf TemperaturInput > 15 And TemperaturInput < 20 Then
PoFResult = 3
ElseIf TemperaturInput > 20 And TemperaturInput < 25 Then
PoFResult = 2
ElseIf TemperaturInput > 25 Then
PoFResult = 1
End If

But waht about 15, 20 and 25. You test for > and < but not =.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

VBA evaluates your conditions in order and takes the first one that is True.
17 satisfies the condition "ElseIf TemperaturInput > 10 Or TemperaturInput <
15" (because it is greater than 10) so VBA applies the result PoFResult = 4
and then figures it is done evaluating your If statement. Actually, I think
you really meant to say "AND" where you have your "OR"s, but it is still
problematic - for one thing, if you have a TemperaturInput exactly equal to
10, 15, 20, or 25 you will not get any value for PoFResult.

There are many ways to do this, but you could simplify it quite a bit if you
do it like this:
PoFResult = 5
If TemperaturInput > 10 Then PoFResult = 4
If TemperaturInput > 15 Then PoFResult = 3
If TemperaturInput > 20 Then PoFResult = 2
If TemperaturInput > 25 Then PoFResult = 1

 

[Guest]

Thank's

I didn't think about the exact numbers 15, 20 etc

Could i use something like this.
If TemperaturInput <= 10 Then
PoFResult = 5
ElseIf TemperaturInput >= 10 And TemperaturInput =< 15 Then
PoFResult = 4

or

If TemperaturInput <10 Then
PoFResult = 5
elseif TemperaturInput = 10 then
PoFResult = 5

 

[keepITcool]

Some alternatives:

Function TempIt(ByVal Temp%) As Integer
TempIt = Application.Lookup(Temp, _
Array(-1E+99, 10, 15, 20, 25), _
Array(5, 4, 3, 2, 1))
End Function

Function TempIt2(ByVal Temp%) As Integer
Dim iRes%
Select Case Temp
Case Is < 10: iRes = 5
Case Is < 15: iRes = 4
Case Is < 20: iRes = 3
Case Is < 25: iRes = 2
Case Else: iRes = 1
End Select
TempIt2 = iRes
End Function

Function TempIt3(ByVal Temp%) As Integer
Dim iRes%
If Temp < 10 Then
iRes = 5
ElseIf Temp < 15 Then
iRes = 4
ElseIf Temp < 20 Then
iRes = 3
ElseIf Temp < 25 Then
iRes = 2
Else
iRes = 1
End If
TempIt3 = iRes
End Function



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Michael wrote :

 

[Tom Ogilvy]

The check is made progressively, so you don't need a lower bound


If TemperaturInput <= 10 Then
PoFResult = 5
ElseIf TemperaturInput =< 15 Then
PoFResult = 4

10 or less would be assigned a 5 in this case because it meets the first
criteria. Anything greater than 10 would go to the EndIF to be evaluated.
Items Less than or equal to 15 (but only those greater than 10 would get
this far) would be assigned 4