Error Tying To Assign A Time Value TO A Variable

[Ark68]

I am getting a 'type mismatch error' with the red highlighted line in my code below.

Sub pda_assign1()
    Dim srow As Integer
    Dim pnum As String
    Dim fac2 As String
    Dim nrec As Double
    Dim st As Date
   
    With ws_thold
        'master static staff list
        .Range("A:E").ClearContents
        'A - Shift; B - Name; C - Crew; D - time on; E - time off
        drow = 1
        For I = 12 To 33 'source
            If ws_master.Cells(I, 22) <> "" Then
                .Cells(drow, 1) = ws_master.Cells(I, 19) 'shift (s)
                .Cells(drow, 2) = ws_master.Cells(I, 23) 'name (w)
                .Cells(drow, 3) = ws_master.Cells(I, 22) 'crew (v)
                .Cells(drow, 4) = ws_master.Cells(I, 20) 'time on (d)
                .Cells(drow, 5) = ws_master.Cells(I, 21) 'time off (e)
                drow = drow + 1
            End If
        Next I
    End With
   
    With ws_master
        nrec = Application.WorksheetFunction.CountA(.Range("C12:C37"))
        If nrec = 0 Then
            MsgBox "No rentals to assign."
            'proceed to services assignments
            Stop
            Exit Sub
        End If
        For srow = 13 To 13 + nrec
            btype = .Cells(srow, 2)
            pnum = .Cells(srow, 3)
            fac2 = .Cells(srow, 4) 'LABEL (col6) in core_data
            st = .Cells(srow, 5).Value
            Stop
            If btype Like "F*" Then
                signatures srow, pnum, fac2, nrec, st
            ElseIf btype Like "D*" Then
           
            ElseIf btype Like "C*" Then
           
            ElseIf btype Like "G*" Then
           
            ElseIf btype Like "T*" Then
           
            ElseIf btype Like "S*" Then
           
            Else
                MsgBox "Error: pda_assign1"
                Stop
            End If
        Next srow
    End With
   
End Sub

The data in worksheet ws_master, column e are times. I am trying to assign variable st to the decimal value of the true time that is in the cell (srow,5).

 

[GWteB]

Could it be that at some point your nrec variable evaluates to a value in a way that your srow variable represents a row where no time value is found?

 

[JoeMo]

What happens if you dimension st as Variant?

 

[Ark68]

Thanks folks for stepping up in your effort to help. I've packed up for the morning but will follow up with testing in the AM.

Could it be that at some point your nrec variable evaluates to a value in a way that your srow variable represents a row where no time value is found?

GWteb I'm sorry. I tried to digest this but not quite sure what you mean

 

[Ark68]

What happens if you dimension st as Variant?

That worked JoeMo. Thank you.

 

[GWteB]

Could it be that at some point your nrec variable evaluates to a value in a way that your srow variable represents a row where no time value is found?

GWteb I'm sorry. I tried to digest this but not quite sure what you mean

I will try to explain this. In your code the nrec variable affects the srow variable, which could lead to an unexpected result for srow:

    With ws_master
        nrec = Application.WorksheetFunction.CountA(.Range("C12:C37"))      ' <<<< NREC
        If nrec = 0 Then
            MsgBox "No rentals to assign."
            'proceed to services assignments
            Stop
            Exit Sub
        End If
        For srow = 13 To 13 + nrec           ' <<<< SROW + NREC
            btype = .Cells(srow, 2)
            pnum = .Cells(srow, 3)
            fac2 = .Cells(srow, 4) 'LABEL (col6) in core_data
            st = .Cells(srow, 5).Value        ' << SROW (gives you an unexpected error in your attempt to pull a date)
            Stop

Anyway, glad you've got a solution.

 

[Ark68]

Ohhh ... yes. I see that now GWteB. And ... is likely the source to the problem. The FOR statement actually includes one too many rows, the access being empty (including in column E). I adjusted that line to ensure only the actual number of occupied rows are included in the loop.

For srow = 13 To 12 + nrec

 

[GWteB]

Thanks for your clarification.

 

[JoeMo]

That worked JoeMo. Thank you.

You are welcome - thanks for the reply.