Run-time error:'13' Type Mismatch

[Eric Amtsberg]

I'm running a dashboard spreadsheet that imports a data file and parses the timestamps. I'm getting a run-time error 13 and I have no idea how to troubleshoot the macro doing the timestamp conversion. Can anyone help?

It appears this is causing the problem

.Cells(i, 8).Value = TimeValue(Left(Right(wksRawData.Cells(i, 1), 8), 4) & "0")

in this macro

End With

' parse day, date and time
For i = 2 To intRows
With wksRawData
    .Cells(i, 6).Value = _
        Weekday(DateValue(Left(wksRawData.Cells(i, 1), 10)))
    .Cells(i, 7).Value = _
        DateValue(Left(wksRawData.Cells(i, 1), 10))
    If Len(wksRawData.Cells(i, 1).Value) > 12 Then
       .Cells(i, 8).Value = TimeValue(Left(Right(wksRawData.Cells(i, 1), 8), 4) & "0")
    Else
        .Cells(i, 8).Value = TimeValue("00:00")
    End If
    .Range("F1:H1").Value = Array("Day", "Date", "Time")
End With
Next i

Here's the dashboard file:
https://www.dropbox.com/s/orxxdcfdvnuzu81/Analytics Dashboard.xlsm?dl=0

Data file I'm importing to the dashboard is attached.

 

[Nebu]

Hi:

I have put an error handler to capture this error, But I am not completely sure what kind of formatting you are doing with the time stamp. I can point out the cause of the error using an extract from your data.


9/10/2015 4:01:27 AM
becomes 1:20:00 AM
but when it comes to
9/10/2015 2:57:24 AM
it converts it into 57:20:00 AM/PM, the macro errors out at this point since this is not a legitimate time value.

I am attaching a copy of file with error handler. Hope this helps.

Thanks

 

[vletm]

Why do You use 'Left & Right'-functions?
You could do like this sample...

    a_tab = ActiveSheet.Name
    For y = 4 To y_max
        b = Worksheets(a_tab).Cells(y, 2)
        w = Weekday(b)
        d = DateValue(b)
        h = TimeValue(b)
    Next y

'just modify like'
b => wksRawData.cells(i,1)
w => .cells(i,6)
d => .cells(i,7)
h => .cells(i,8)