Can I use Select Case to do this?

[ChipButtyMan]

Hi,
I guess it would be better to use Select Case statement to do this
but I don't know how I would write the code.
MyVa is a variable that contains an integer (that represents a
workday)
Thank you for your help, time & expertise.


If Range("C" & i).Value = MyVa And Range("D" & i).Value = "Ready to
ship" Then
Range("E" & i).Value = "Right on time"
End If

If Range("C" & i).Value < MyVa And Range("D" & i).Value = "Ready to
ship" Then
Range("E" & i).Value = "Missed Code Date"
End If

If Range("B" & i).Value = MyVa + 1 And Range("D" & i).Value =
"Ready to ship" Then
Range("E" & i).Value = "One day early"
End If

If Range("B" & i).Value = MyVa + 2 And Range("D" & i).Value =
"Ready to ship" Then
Range("E" & i).Value = "Two days early"
End If

If Range("B" & i).Value = MyVa And Range("D" & i).Value =
"Outstanding work" Then
Range("E" & i).Value = "One day late"
End If

 

[Bob Phillips]

Select Case True

Case Range("C" & i).Value = MyVa And _
Range("D" & i).Value = "Ready to ship "
Range("E" & i).Value = "Right on time"

Case Range("C" & i).Value < MyVa And _
Range("D" & i).Value = "Ready to ship "
Range("E" & i).Value = "Missed Code Date"

Case Range("B" & i).Value = MyVa + 1 And _
Range("D" & i).Value = "Ready to ship"
Range("E" & i).Value = "One day early"

Case Range("B" & i).Value = MyVa + 2 And _
Range("D" & i).Value = "Ready to ship"
Range("E" & i).Value = "Two days early"

Case Range("B" & i).Value = MyVa And _
Range("D" & i).Value = "Outstanding work"
Range("E" & i).Value = "One day late"
End Select

 

[Peter T]

I think the following looks like what you are trying to do but double check
carefully, because I haven't tested at all !

If Range("D" & i).Value = "Ready to ship " Then
Select Case Range("C" & i).Value

Case myVa
Range("E" & i).Value = "Right on time"
Case Is < myVa
Range("E" & i).Value = "Missed Code Date"
Case myVa + 1
Range("E" & i).Value = "One day early"
Case myVa + 1
Range("E" & i).Value = "Two days early"

End Select

ElseIf Range("D" & i).Value = "Outstanding work" Then
If Range("B" & i).Value = myVa Then
Range("E" & i).Value = "One day late" ' ?

End If

End If

Regards,
Peter T

 

[ChipButtyMan]

Thank you very much indeed for your help Bob.
Very much obliged

 

[ChipButtyMan]

Thanks very much for your time & supplying the code Peter. Much
appreciated indeed.

 

[ChipButtyMan]

I can't get either of the codes to work, I'm sure it's something
simple but not simple enough for me to fix :-(

My sheet looks like this

Column A = Truck serial numbers
Column B = Shipping dates
Column C = Numerical value representing shipping date
Column D = Truck status (ie. Ready to ship, Outstanding work etc)
Column E = the column that the Select Case statement will populate
(Right on time, One day early etc.)

Here is my code that works, but I guess is not the best way to do it?
(the Select Case statement will be huge by the time I've added all the
senarios!)
Thanks again everyone;

Private Sub CommandButton3_Click()
Dim i As Integer
Dim r As Integer
Dim MyVa As Integer
Dim TodaysDate As Date

TodaysDate = Date
r = Range("A65536").End(xlUp).Row

For i = 2 To r
If Range("B" & i).Value = TodaysDate Then
MyVa = Range("C" & i).Value
End If
Next i


For i = 2 To r
If Range("C" & i).Value = MyVa And Range("D" & i).Value =
"Ready to ship" Then
Range("E" & i).Value = "Right on time"
End If

If Range("C" & i).Value < MyVa And Range("D" & i).Value =
"Ready to ship" Then
Range("E" & i).Value = "Missed Code Date"
End If

If Range("B" & i).Value = MyVa + 1 And Range("D" & i).Value
= "Ready to ship" Then
Range("E" & i).Value = "One day early"
End If

If Range("B" & i).Value = MyVa + 2 And Range("D" & i).Value
= "Ready to ship" Then
Range("E" & i).Value = "Two days early"
End If

If Range("B" & i).Value = MyVa And Range("D" & i).Value =
"Outstanding work" Then
Range("E" & i).Value = "One day late"
End If


Next i

End Sub

 

[Bob Phillips]

Maybe you want this, but two questions, are you sure tat the strings have a
space at the end, and is the case (upper an d lower that is) all accurate?

Private Sub CommandButton3_Click()
Dim i As Long
Dim r As Long
Dim MyVa As Integer
Dim TodaysDate As Date

TodaysDate = Date
r = Range("A65536").End(xlUp).Row

i = 2
Do While Range("B" & i).Value <> TodaysDate And i < r + 1
i = i + 1
Loop
If i <= r Then MyVa = Range("C" & i).Value

For i = 2 To r
Select Case True

Case Range("C" & i).Value = MyVa And _
Range("D" & i).Value = "Ready to ship "
Range("E" & i).Value = "Right on time"

Case Range("C" & i).Value < MyVa And _
Range("D" & i).Value = "Ready to ship "
Range("E" & i).Value = "Missed Code Date"

Case Range("B" & i).Value = MyVa + 1 And _
Range("D" & i).Value = "Ready to ship"
Range("E" & i).Value = "One day early"

Case Range("B" & i).Value = MyVa + 2 And _
Range("D" & i).Value = "Ready to ship"
Range("E" & i).Value = "Two days early"

Case Range("B" & i).Value = MyVa And _
Range("D" & i).Value = "Outstanding work"
Range("E" & i).Value = "One day late"
End Select
Next i

End Sub

 

[ChipButtyMan]

Wow thanks a million times Bob!
Works perfectly & you just made my day.
Thanks again for your time & expertise!