If...Then Problem - bringing up wrong info!

**gunman** · 06-06-2005, 12:47 PM

Everyone,

I am trying to make an IF...THEN statement that will use different sets of equations based on what the user uses as an input. However, the program brings up the wrong equations for the input sets. I feel that the problem probably exists in the If...Then statement. I'm pretty new at this so if anyone has any suggestions, please let me know. I'm attaching the piece of code below.
Thanks,

Gunman

'Routing information to the correct Rjc equations based on "Wdth" size and "GP"
If Wdth < 550 And GP < 15 Then
Rjc = Rjc5001
ElseIf Wdth < 550 And GP > 20 Then
Rjc = Rjc5002
ElseIf Wdth < 550 And 15 <= GP <= 20 Then
Rjc = Rjc5003
ElseIf 550 <= Wdth < 650 And GP < 15 Then
Rjc = Rjc6001
ElseIf 550 <= Wdth < 650 And GP > 20 Then
Rjc = Rjc6002
ElseIf 550 <= Wdth < 650 And 15 <= GP <= 20 Then
Rjc = Rjc6003
ElseIf 650 <= Wdth < 750 And GP < 15 Then
Rjc = Rjc7001
ElseIf 650 <= Wdth < 750 And GP > 20 Then
Rjc = Rjc7002
ElseIf 650 <= Wdth < 750 And 15 <= GP <= 20 Then
Rjc = Rjc7003
ElseIf 750 <= Wdth <= 850 And GP < 15 Then
Rjc = Rjc8001
ElseIf 750 <= Wdth <= 850 And GP > 20 Then
Rjc = Rjc8002
ElseIf 750 <= Wdth <= 850 And 15 <= GP <= 20 Then
Rjc = Rjc8003
Else: Rjc = 0
End If

**gunman** · 06-06-2005, 01:31 PM

any thoughts??? any input would be greatly appreciated

CR · 06-06-2005, 02:05 PM

gunman wrote:
> Everyone,
>
> I am trying to make an IF...THEN statement that will use different sets
> of equations based on what the user uses as an input. However, the
> program brings up the wrong equations for the input sets. I feel that
> the problem probably exists in the If...Then statement. I'm pretty new
> at this so if anyone has any suggestions, please let me know. I'm
> attaching the piece of code below.
> Thanks,
>

> Gunman
>
> 'Routing information to the correct Rjc equations based on "Wdth" size
> and "GP"
> If Wdth < 550 And GP < 15 Then
> Rjc = Rjc5001
> ElseIf Wdth < 550 And GP > 20 Then
> Rjc = Rjc5002
> ElseIf Wdth < 550 And 15 <= GP <= 20 Then
> Rjc = Rjc5003
> ElseIf 550 <= Wdth < 650 And GP < 15 Then
> Rjc = Rjc6001
> ElseIf 550 <= Wdth < 650 And GP > 20 Then
> Rjc = Rjc6002
> ElseIf 550 <= Wdth < 650 And 15 <= GP <= 20 Then
> Rjc = Rjc6003
> ElseIf 650 <= Wdth < 750 And GP < 15 Then
> Rjc = Rjc7001
> ElseIf 650 <= Wdth < 750 And GP > 20 Then
> Rjc = Rjc7002
> ElseIf 650 <= Wdth < 750 And 15 <= GP <= 20 Then
> Rjc = Rjc7003
> ElseIf 750 <= Wdth <= 850 And GP < 15 Then
> Rjc = Rjc8001
> ElseIf 750 <= Wdth <= 850 And GP > 20 Then
> Rjc = Rjc8002
> ElseIf 750 <= Wdth <= 850 And 15 <= GP <= 20 Then
> Rjc = Rjc8003
> Else: Rjc = 0
> End If
>
>
> --
> gunman
> ------------------------------------------------------------------------
> gunman's Profile: http://www.excelforum.com/member.php...o&userid=23994
> View this thread: http://www.excelforum.com/showthread...hreadid=376820

' try this:
'*********** NOT TESTED
Rjc = 0
Select Case wdth
Case Is < 550
Select Case gp
Case Is < 15
Rjc = rjc5001
Case Is > 20
Rjc = rjc5002
Case Else
Rjc = rjc5003
End Select
Case Is < 650
Select Case gp
Case Is < 15
Rjc = rjc6001
Case Is > 20
Rjc = rjc6002
Case Else
Rjc = rjc6003
End Select
Case Is < 750
Select Case gp
Case Is < 15
Rjc = rjc7001
Case Is > 20
Rjc = rjc7002
Case Else
Rjc = rjc7003
End Select
Case Is <= 850
Select Case gp
Case Is < 15
Rjc = rjc8001
Case Is > 20
Rjc = rjc8002
Case Else
Rjc = rjc8003
End Select
End Select

CR · 06-06-2005, 02:05 PM

Rjc = 0
Select Case wdth
Case Is < 550
Select Case gp
Case Is < 15
Rjc = rjc5001
Case Is > 20
Rjc = rjc5002
Case Else
Rjc = rjc5003
End Select
Case Is < 650
Select Case gp
Case Is < 15
Rjc = rjc6001
Case Is > 20
Rjc = rjc6002
Case Else
Rjc = rjc6003
End Select
Case Is < 750
Select Case gp
Case Is < 15
Rjc = rjc7001
Case Is > 20
Rjc = rjc7002
Case Else
Rjc = rjc7003
End Select
Case Is <= 850
Select Case gp
Case Is < 15
Rjc = rjc8001
Case Is > 20
Rjc = rjc8002
Case Else
Rjc = rjc8003
End Select
End Select

STEVE BELL · 06-06-2005, 02:05 PM

Try this:
but what happens if wdth or GP is not a number?

If wdth < 550 Then
If GP < 15 Then
Rjc = rjc5001
ElseIf GP > 20 Then
Rjc = rjc5002
Else
Rjc = rjc5003
End If
ElseIf wdth < 650 Then
If GP < 15 Then
Rjc = rjc6001
ElseIf GP > 20 Then
Rjc = rjc6002
Else
Rjc = rjc6003
End If
ElseIf wdth < 750 Then
If GP < 15 Then
Rjc = rjc7001
ElseIf GP > 20 Then
Rjc = rjc7002
Else
Rjc = rjc7003
End If
ElseIf wdth <= 850 Then
If GP < 15 Then
Rjc = rjc8001
ElseIf GP > 20 Then
Rjc = rjc8002
Else
Rjc = rjc8003
End If
Else
Rjc = 0
End If
End Sub

--
steveB

Remove "AYN" from email to respond
"gunman" <[email protected]> wrote in
message news:[email protected]...
>
> Everyone,
>
> I am trying to make an IF...THEN statement that will use different sets
> of equations based on what the user uses as an input. However, the
> program brings up the wrong equations for the input sets. I feel that
> the problem probably exists in the If...Then statement. I'm pretty new
> at this so if anyone has any suggestions, please let me know. I'm
> attaching the piece of code below.
> Thanks,
>

> Gunman
>
> 'Routing information to the correct Rjc equations based on "Wdth" size
> and "GP"
> If Wdth < 550 And GP < 15 Then
> Rjc = Rjc5001
> ElseIf Wdth < 550 And GP > 20 Then
> Rjc = Rjc5002
> ElseIf Wdth < 550 And 15 <= GP <= 20 Then
> Rjc = Rjc5003
> ElseIf 550 <= Wdth < 650 And GP < 15 Then
> Rjc = Rjc6001
> ElseIf 550 <= Wdth < 650 And GP > 20 Then
> Rjc = Rjc6002
> ElseIf 550 <= Wdth < 650 And 15 <= GP <= 20 Then
> Rjc = Rjc6003
> ElseIf 650 <= Wdth < 750 And GP < 15 Then
> Rjc = Rjc7001
> ElseIf 650 <= Wdth < 750 And GP > 20 Then
> Rjc = Rjc7002
> ElseIf 650 <= Wdth < 750 And 15 <= GP <= 20 Then
> Rjc = Rjc7003
> ElseIf 750 <= Wdth <= 850 And GP < 15 Then
> Rjc = Rjc8001
> ElseIf 750 <= Wdth <= 850 And GP > 20 Then
> Rjc = Rjc8002
> ElseIf 750 <= Wdth <= 850 And 15 <= GP <= 20 Then
> Rjc = Rjc8003
> Else: Rjc = 0
> End If
>
>
> --
> gunman
> ------------------------------------------------------------------------
> gunman's Profile:
> http://www.excelforum.com/member.php...o&userid=23994
> View this thread: http://www.excelforum.com/showthread...hreadid=376820
>

STEVE BELL · 06-06-2005, 03:05 PM

There were several replies. Check around for them.

But here's a repeat of my offering:

If wdth < 550 Then
If GP < 15 Then
Rjc = rjc5001
ElseIf GP > 20 Then
Rjc = rjc5002
Else
Rjc = rjc5003
End If
ElseIf wdth < 650 Then
If GP < 15 Then
Rjc = rjc6001
ElseIf GP > 20 Then
Rjc = rjc6002
Else
Rjc = rjc6003
End If
ElseIf wdth < 750 Then
If GP < 15 Then
Rjc = rjc7001
ElseIf GP > 20 Then
Rjc = rjc7002
Else
Rjc = rjc7003
End If
ElseIf wdth <= 850 Then
If GP < 15 Then
Rjc = rjc8001
ElseIf GP > 20 Then
Rjc = rjc8002
Else
Rjc = rjc8003
End If
Else: Rjc = 0
End If
End Sub

--
steveB

Remove "AYN" from email to respond
"gunman" <[email protected]> wrote in
message news:[email protected]...
>
> any thoughts??? any input would be greatly appreciated
>
>
> --
> gunman
> ------------------------------------------------------------------------
> gunman's Profile:
> http://www.excelforum.com/member.php...o&userid=23994
> View this thread: http://www.excelforum.com/showthread...hreadid=376820
>

Dana DeLouis · 06-07-2005, 09:05 AM

Would something like "Rjc8002" be a macro? If so, then here is a different
approach. This doesn't have much error checking.

Sub Demo()
Dim N1, N2, wdth, gp, equ
wdth = 800
gp = 20.1

If wdth <= 0 Or wdth > 850 Then Exit Sub
If gp <= 0 Then Exit Sub

N1 = Abs(5 * (wdth >= 0) + (wdth >= 550) + (wdth >= 650) + (wdth >=
750))
N2 = 1 - 2 * (gp >= 15) + (gp > 20)

equ = Replace("rjc#00#", "#", N1, 1, 1)
equ = Replace(equ, "#", N2, 1, 1)

Run equ
End Sub

Note that Excel vba can not do that type of test you were doing as in some
other languages (a<x<b). Here is an example of what you were trying to
do...

If 550 <= wdth And wdth < 650 And gp < 15 Then
'...your code
End If

HTH :>)
--
Dana DeLouis
Win XP & Office 2003

"gunman" <[email protected]> wrote in
message news:[email protected]...
>
> Everyone,
>
> I am trying to make an IF...THEN statement that will use different sets
> of equations based on what the user uses as an input. However, the
> program brings up the wrong equations for the input sets. I feel that
> the problem probably exists in the If...Then statement. I'm pretty new
> at this so if anyone has any suggestions, please let me know. I'm
> attaching the piece of code below.
> Thanks,
>

> Gunman
>
> 'Routing information to the correct Rjc equations based on "Wdth" size
> and "GP"
> If Wdth < 550 And GP < 15 Then
> Rjc = Rjc5001
> ElseIf Wdth < 550 And GP > 20 Then
> Rjc = Rjc5002
> ElseIf Wdth < 550 And 15 <= GP <= 20 Then
> Rjc = Rjc5003
> ElseIf 550 <= Wdth < 650 And GP < 15 Then
> Rjc = Rjc6001
> ElseIf 550 <= Wdth < 650 And GP > 20 Then
> Rjc = Rjc6002
> ElseIf 550 <= Wdth < 650 And 15 <= GP <= 20 Then
> Rjc = Rjc6003
> ElseIf 650 <= Wdth < 750 And GP < 15 Then
> Rjc = Rjc7001
> ElseIf 650 <= Wdth < 750 And GP > 20 Then
> Rjc = Rjc7002
> ElseIf 650 <= Wdth < 750 And 15 <= GP <= 20 Then
> Rjc = Rjc7003
> ElseIf 750 <= Wdth <= 850 And GP < 15 Then
> Rjc = Rjc8001
> ElseIf 750 <= Wdth <= 850 And GP > 20 Then
> Rjc = Rjc8002
> ElseIf 750 <= Wdth <= 850 And 15 <= GP <= 20 Then
> Rjc = Rjc8003
> Else: Rjc = 0
> End If
>
>
> --
> gunman
> ------------------------------------------------------------------------
> gunman's Profile:
> http://www.excelforum.com/member.php...o&userid=23994
> View this thread: http://www.excelforum.com/showthread...hreadid=376820
>