asked on

Excel VB Greater-than or Lesser-than Comparison Statement

Hello Experts,

I am having trouble with my if statements in a Excel VB macro - I think all the if statements are returning true and the macro is accomplishing just the last statement.

'Sub RudderCableMin()

'Rudder Cable Min Slope Intercept

Dim Rmin As Double ' Minimum Rudder Cable Tension in LBS

Rmin = Range("A9").Value

'If 90 > Rmin < 100 Then _
Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D18) + Sheet3!E18"

If 100 > Rmin < 120 Then _
Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D19) + Sheet3!E19"

'If 120 > Rmin < 140 Then _
Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D20) + Sheet3!E20"

'If 140 > Rmin < 160 Then _
Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D21) + Sheet3!E21"

'End Sub

Thanks

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HiepTest

I think VB does not understand if 90 < Rmin statement
your proc should be rewrote like this:

HiepTest

here is the code

Sub RudderCableMin()

'Rudder Cable Min Slope Intercept

Dim Rmin As Double ' Minimum Rudder Cable Tension in LBS


Rmin = Range("A9").Value
    If Rmin > 90 Then
        If Rmin > 100 Then
            If Rmin > 120 Then
                If Rmin > 140 Then
                    If Rmin < 160 Then
                        Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D21) + Sheet3!E21"
                    End If
                Else
                    Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D20) + Sheet3!E20"
                End If
            Else
                Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D19) + Sheet3!E19"
            End If
        Else
            Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D18) + Sheet3!E18"
        End If
    End If
End Sub

Open in new window

Chris Bottomley

I agree with Kevin BUT I depending on teh scope of your code then perhaps you mean it as one level of construct and perhaps as a nested construct. The following is represented as a single level construct but with elseif instead of multiple if's and the end if termination.

Chris

    If Rmin >= 90 And Rmin < 100 Then _
      Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D18) + Sheet3!E18"
        
    ElseIf Rmin >= 100 And Rmin < 120 Then _
      Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D19) + Sheet3!E19"
    
    ElseIf Rmin >= 120 And Rmin < 140 Then _
      Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D20) + Sheet3!E20"
    
    ElseIf Rmin >= 140 And Rmin < 160 Then _
      Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D21) + Sheet3!E21"
    End If

Open in new window

zorvek (Kevin Jones)

Select Case Rmin
Case 90 To 99: Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D18) + Sheet3!E18"
Case 100 To 119: Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D19) + Sheet3!E19"
Case 120 To 139: Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D20) + Sheet3!E20"
Case 140 To 159: Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D21) + Sheet3!E21"
Case Else: Range("A12").Formula = "= (Sheet1!A9 * Sheet3!D21) + Sheet3!E21"
End Select

Kevin

gecf343b1

ASKER

Thanks - it works great