VBA Case Select Multiple Conditions

[I3atnumb3rs]

Hello,

I'm trying to do a case statement in VBA. If the value in J is >= 200 AND <300 I want it to say in O REGULAR DELIVERY PV, when >= 300 AND <400 I want it to say REGULAR DELIVERY PV300, and when >= 400 REGULAR DELIVERY PV400. This does not work.

Sub VolumeChange()


'
Application.ScreenUpdating = False

'Changes delivery type


Dim x As Long

For x = 2 To Cells(Rows.Count, "J").End(xlUp).Row
Select Case UCase(Cells(x, 10))

Case Is >= 198
Cells(x, 15).FormulaR1C1 = "REGULAR DELIVERY PV"
Case Is >= 300
Cells(x, 15).FormulaR1C1 = "REGULAR DELIVERY PV300"
Case Is >= 400
Cells(x, 15).FormulaR1C1 = "REGULAR DELIVERY PV400"

End Select
Next x

End Sub

 

[mrshl9898]

You'll need to look for the largest value first.

Sub VolumeChange()




'
Application.ScreenUpdating = False


'Changes delivery type




Dim x As Long


For x = 2 To Cells(Rows.Count, "J").End(xlUp).Row
Select Case UCase(Cells(x, 10))


Case Is >= 400
Cells(x, 15).FormulaR1C1 = "REGULAR DELIVERY PV400"
Case Is >= 300
Cells(x, 15).FormulaR1C1 = "REGULAR DELIVERY PV300"
Case Is >= 198
Cells(x, 15).FormulaR1C1 = "REGULAR DELIVERY PV"


End Select
Next x


End Sub

 

[Fluff]

Another option

Sub VolumeChange()
   Dim x As Long
   Dim Vu As String
   
   For x = 2 To Cells(Rows.Count, "J").End(xlUp).Row
      Select Case Cells(x, 10)
         Case Is >= 400
            Vu = "REGULAR DELIVERY PV400"
         Case 300 To 400
            Vu = "REGULAR DELIVERY PV300"
         Case 200 To 300
            Vu = "REGULAR DELIVERY PV"
      End Select
         Cells(x, 15).Value = Vu
   Next x
End Sub

 

[I3atnumb3rs]

Brilliant, is there a reason why it needs to go from greatest value to smallest?

 

[mrshl9898]

If it's 350 then it's greater than 200 so that condition is met and you get the result. In the reverse it's not greater than 400, but greater than 300 so you get the result.