Type Mismatch when pasting multiple cells

Question

I am trying to paste a time worked database into an excel file which will auto-calculate how long each job took and the total amount of hours worked. When I copy/paste one cell at a time, everything works fine. When I try copying 2 or more cells, I get a "Run-time error '13': Type Mismatch" error on:

Tlen = Len(Target)

Here is a screen capture video of what I am working with: https://youtu.be/Qhd37U3SLQU

What I ultimately am trying to do is at 1:05 of the video. Essentially, I am trying to paste a block of info from a database into my excel file and filter out anything that is not time. Column D calculates the duration between start and stop time and E2 would have the sum of column D.



My current code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not IsNumeric(Target) Then
    'do nothing
End If

If Not Intersect(Target, Range("a2:c900" & ThisWorkbook.Worksheets(1).UsedRange.Rows.Columns.Count)) Is Nothing Then
    Tlen = Len(Target)
    Targval = Target.Value

If Tlen = 1 Then
        TimeV = TimeValue("00:" & Target)


ElseIf Tlen = 2 And Targval < 60 Then
    TimeV = TimeValue("00:" & Target)
ElseIf Tlen = 2 And 60 <= Targval Then
    'do nothing


        ElseIf Tlen = 3 And Targval >= 160 And Targval <= 199 Or Targval >= 260 And Targval <= 299 Or Targval >= 360 And Targval <= 399 Or Targval >= 460 And Targval <= 499 Then
            'do nothing
        ElseIf Tlen = 3 And Targval >= 560 And Targval <= 599 Or Targval >= 660 And Targval <= 699 Or Targval >= 760 And Targval <= 799 Or Targval >= 860 And Targval <= 899 Then
            'do nothing
        ElseIf Tlen = 3 And Targval >= 960 And Targval <= 999 Then
            'do nothing
ElseIf Tlen = 3 Then
    TimeV = TimeValue(Left(Target, 1) & ":" & Right(Target, 2))


        ElseIf Tlen = 4 And Targval >= 1060 And Targval <= 1099 Or Targval >= 1160 And Targval <= 1199 Or Targval >= 1260 And Targval <= 1299 Or Targval >= 1360 And Targval <= 1399 Then
            'do nothing
        ElseIf Tlen = 4 And Targval >= 1460 And Targval <= 1499 Or Targval >= 1560 And Targval <= 1599 Or Targval >= 1660 And Targval <= 1699 Or Targval >= 1760 And Targval <= 1799 Then
            'do nothing
        ElseIf Tlen = 4 And Targval >= 1860 And Targval <= 1899 Or Targval >= 1960 And Targval <= 1999 Or Targval >= 2060 And Targval <= 2099 Or Targval >= 2160 And Targval <= 2199 Then
            'do nothing
        ElseIf Tlen = 4 And Targval >= 2260 And Targval <= 2299 Or Targval >= 2360 And Targval <= 2399 Then
            'do nothing
ElseIf Tlen = 4 And Targval < 2400 Then
    TimeV = TimeValue(Left(Target, 2) & ":" & Right(Target, 2))
        ElseIf Tlen >= 4 And Targval >= 2400 Then
            'do nothing

End If

Target.NumberFormat = "HHMM"

Application.EnableEvents = False
Target = TimeV
Application.EnableEvents = True

End If

End Sub

Answer 1

Your existing code is a bit confusing when supplied without sample data and expected results but I believe this is what you are trying to do.

_{Take a 1 to 4 digit number from Base 10 and convert it to time in HHMM format.}

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("a:c"), Me.UsedRange) Is Nothing Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        Dim t As Range
        For Each t In Intersect(Target, Range("a:c"), Me.UsedRange)
            If IsNumeric(t) And LCase(t.NumberFormat) <> "hhmm" And LCase(t.NumberFormat <> "[hh]mm") Then
                t.NumberFormat = "[hh]mm"
                t = TimeSerial(Int(t.Value / 100), (t.Value Mod 100), 0)
                'alternate
                't = TimeSerial(Int(val(t.text) / 100), (val(t.text) Mod 100), 0)
                't.NumberFormat = "[hh]mm"
            End If
        Next t
    End If

safe_exit:
    Application.EnableEvents = True

End Sub