| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use Select Case to do this?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I use Select Case to do this?
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 -- __________________________________ HTH Bob "ChipButtyMan" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I use Select Case to do this?
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I use Select Case to do this?
Thank you very much indeed for your help Bob.
Very much obliged |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I use Select Case to do this?
Thanks very much for your time & supplying the code Peter. Much
appreciated indeed. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I use Select Case to do this?
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I use Select Case to do this?
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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can I use Select Case to do this?
Wow thanks a million times Bob!
Works perfectly & you just made my day. Thanks again for your time & expertise! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select Case | Excel Discussion (Misc queries) | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| End Select without Select Case, Block If without End If errors | Excel Programming | |||
| Case Select | Excel Programming | |||
Linear Mode
