Case statement nested in WITH Statement not selecting a case

Question

I am trying to set cell "H2" to either "Shift 1" , "Shift 2" or "Shift 3" depending on the inputted time value found in Cell D2 of my workbook, here is a screenshot example:

So Cell H2 is Shift 1 because it's within a Timevalue of Case TimeValue("11:21 PM") To TimeValue("7:20 AM")

Here is the code, it executes but doesn't select a case and I can't figure out my mistake. Also, if there is anyway to execute these 3 case statements within the With statement as I set the time input in cell "D2" inside of that with statement I would appreciate that!

.Range("D2").Value = Now  'Inputs the Time Value as the current Time                                                                                                                        
        
.Range("D2").NumberFormat = "h:mm:ss AM/PM"  'Formats the Time value as a Time entry

The code can be found below:

Sub ReportGeneratorTest()

    Application.ScreenUpdating = False                                                                                                         'This speeds up the macro by hiding what the macro is doing
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Set wb3 = Workbooks.Open(Filename:="\\Report Generator\SetupSheet Report Generator.xlsm")   'Sets the Workbook variable as the database filepath
    
    With wb3.Sheets("All Requests Sheet 1")                                                                                                    'With the "Changes" sheet do the following

        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow                                                                             'Inserts a new row in [3] with the same format as below
        
        .Range("A2").Value = Sheet1.Range("K112").Value                                                 'Inputs the typed in Operator name into the Report Generator
        
        .Range("B2").Value = Sheet1.Range("H4").Value                                                                                                       'Inputs the "Key" inside cell "A"  of the new row
        
        .Range("C2").Value = Now                                                                                                                            'Inputs the Date Submitted value as the current date
        
        .Range("C2").NumberFormat = "dd-mmm-yyyy"                                                                                                           'Formats the Date Submitted value as a date entry
        
        .Range("D2").Value = Now                                                                                                                            'Inputs the Time Value as the current Time
        
        .Range("D2").NumberFormat = "h:mm:ss AM/PM"                                                                                                         'Formats the Time value as a Time entry
        
        .Range("E2").Value = UCase(Sheet1.Range("E4").Value)                                                                                                'Inputs the Part inside Cell "D" of the new row
        
        .Range("F2").Value = Sheet1.Range("E5").Value                                                                                                       'Inputs the Process inside Cell "E" of the new row
        
        .Range("G2").Value = "IRR 200-2S"
        
    End With
  
    Dim T1 As Date
    
    'T1 = Range("D2").Value
    
    T1 = Now
    
    'Set T1 = Range("D2").Value
        
    Select Case T1
        
        Case TimeValue("7:21 AM") To TimeValue("3:20 PM")
        
         Range("H2").Value = "Shift 2"
        
        Case TimeValue("3:21 PM") To TimeValue("11:20 PM")
        
         Range("H2").Value = "Shift 3"
         
        Case Else 'If the Timevalue is between TimeValue("11:21 PM") To TimeValue("7:20 AM")
        
        Range("H2").Value = "Shift 1"
        
    End Select
    
    wb3.Save                                                                                                                                              'Save the database Workbook
        
    wb3.Close False
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True                                                                                                                    'Must be "True" after running the code to be able to Read/Write the Workbook

End Sub

`Case TimeValue("11:21 PM") To TimeValue("7:20 AM")` doesn't work because in a `To` line, the smaller value should be first. — BigBen, Aug 24 '20 at 19:01
But more importantly, `T1 = Now` = the problem here is that `Now` includes a date portion. You have to strip out the time yourself. — BigBen, Aug 24 '20 at 19:06
@BigBen I've made the adjustment to ```Case TimeValue("11:21 PM") To TimeValue("7:20 AM")``` and replaced it with ```Case Else``` — Handreen, Aug 24 '20 at 19:07
@BigBen is there another way to initialize ```T1``` as a time variable, if not how do I strip out the date and only leave the Time? — Handreen, Aug 24 '20 at 19:11
`Now - Int(Now)` will give you just the time portion. Or `Now - Date`. — BigBen, Aug 24 '20 at 19:11
@BigBen so ```T1 = Int(Now)```? Is it setting the DateTime value as an integer now? — Handreen, Aug 24 '20 at 19:12

score 1 · Accepted Answer · answered Aug 24 '20 at 19:29

Summarizing the comments into an answer:

Case TimeValue("11:21 PM") To TimeValue("7:20 AM") doesn't work because when using To, the smaller value should come first. Maybe just move the "Shift 1" logic to a Case Else.
More importantly, Now includes today's date, i.e. it has both a day and time component. To get only the time component, you could do the following:

Dim T1 As Double
T1 = Now - Date

Case statement nested in WITH Statement not selecting a case

1 Answers1