| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Between to values
Hi!
I have a VB formula that look like this. TemperaturInput=FrmSeawaterParameter25CrSDSS.TxtTe mperature.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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Between to values
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) "Michael" wrote in message ... Hi! I have a VB formula that look like this. TemperaturInput=FrmSeawaterParameter25CrSDSS.TxtTe mperature.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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Between to values
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 -- - K Dales "Michael" wrote: Hi! I have a VB formula that look like this. TemperaturInput=FrmSeawaterParameter25CrSDSS.TxtTe mperature.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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Between to values
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 "Bob Phillips" wrote: 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) "Michael" wrote in message ... Hi! I have a VB formula that look like this. TemperaturInput=FrmSeawaterParameter25CrSDSS.TxtTe mperature.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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Between to values
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 : Hi! I have a VB formula that look like this. TemperaturInput=FrmSeawaterParameter25CrSDSS.TxtTe mperature.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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Between to values
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 -- Regards, Tom Ogilvy "Michael" wrote in message ... 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 "Bob Phillips" wrote: 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) "Michael" wrote in message ... Hi! I have a VB formula that look like this. TemperaturInput=FrmSeawaterParameter25CrSDSS.TxtTe mperature.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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 doesnt show Y-axis values when the values are small. | Charts and Charting in Excel | |||
| Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
| Search/Filter to find values in another range based on two cell values | Excel Programming | |||
| How do I search thr'o column and put unique values in differnt sheet and sum corresponding values in | Excel Programming | |||
| Predict Y-values on new X-values based on other actual X and Y values? | Excel Programming | |||
Linear Mode
