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
--
__________________________________
HTH
Bob
"ChipButtyMan" <(E-Mail Removed)> wrote in message
news:989bee0d-aa31-4cce-9ef0-(E-Mail Removed)...
>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
|
Similar Threads
