If Then Else not working 1

[stillwillyboy]

In the following, the first If with the 180, 181 or 182 works, but as it loops thru, it blows past the others. It ignores the next two If statements. I have verified my data and there are cells with 186, 187, 189 and above.

Sub CalculatePay()
Application.ScreenUpdating = False
Range("A2").Select
Do
With ActiveCell
If ActiveCell = 180 Or 181 Or 182 Then
ActiveCell.Offset(0, 5) = ActiveCell.Offset(0, 1) * _
ActiveCell.Offset(0, 3)

ElseIf ActiveCell > 182 And ActiveCell < 189 Then
ActiveCell.Offset(0, 5) = ActiveCell.Offset(0, 1) * _
ActiveCell.Offset(0, 4)

ElseIf ActiveCell > 189 Then
ActiveCell.Offset(0, 5) = ActiveCell.Offset(0, 2) * _
ActiveCell.Offset(0, 3)
End If
End With
With ActiveCell
.Offset(1, 0).Select
End With
Loop Until ActiveCell = ""
Range("A1").Select
End Sub

TIA

Bill

 

[rmikesmith]

Bill,

The problem is with your first If..Then construct. You must explicitly reference the tested variable for each comparison. The compiler expects each test to evaluate to True or False. According to the VBA Help: When other numeric types are converted to Boolean values, 0 becomes False and all other values become True. Therefore, the single values 180 and 181 evaluate to True and the first If..Then will always be executed. Here is a revised version of your code:

Sub CalculatePay()
   Application.ScreenUpdating = False
   Range("A2").Select
   Do
     With ActiveCell
       If ActiveCell = 180 Or [b]ActiveCell =[/b] 181 Or [b]ActiveCell =[/b] 182 Then
         ActiveCell.Offset(0, 5) = ActiveCell.Offset(0, 1) * ActiveCell.Offset(0, 3)

       ElseIf ActiveCell > 182 And ActiveCell < 189 Then
         ActiveCell.Offset(0, 5) = ActiveCell.Offset(0, 1) * ActiveCell.Offset(0, 4)

       ElseIf ActiveCell > 189 Then
         ActiveCell.Offset(0, 5) = ActiveCell.Offset(0, 2) * ActiveCell.Offset(0, 3)
       End If
       .Offset(1, 0).Select
     End With
   Loop Until ActiveCell = ""
   Range("A1").Select

End Sub

Regards,
Mike