Type mismatch error VBA

Question

I am trying to use VBA to look for the time difference between two columns. I have checked that both columns have the same data type however whenever I run the macro, it keeps giving me type mismatch error. I cannot figure out why so any help would be appreciated.

  Sub timeshfiter()
  lastrow = Application.CountA(Range("A:A"))
  For i = 2 To lastrow
  If (Worksheets("Sheet1").Cells(i, "A").Value = Worksheets("Sheet2").Cells(i,"A").Value)  Then
        If ((Abs(Worksheets("Sheet1").Cells(i, "I").Value - Worksheets("Sheet2").Cells(i, "I").Value) * 86400) <= TimeValue(20)) Then
    Worksheets("Sheet1").Cells(i, "K").Value = Worksheets("Sheet2").Cells(i,   "I").Value
    Else: Worksheets("Sheet1").Cells(i, "K").Value = "Check"
         End If
  End If

    Next i

    End Sub

R.Katnaan R.Katnaan · Accepted Answer · 2015-08-17T07:35:06

I found the main error. You use TimeValue(20) in condition line. That cause Type mismatch error.

Here, references (1) (2) for using TimeValue method.

Here is a sample for valid usage with your code:

Sub timeshfiter()

    lastrow = Application.CountA(Range("A:A"))

    For i = 2 To lastrow

        If Worksheets("Sheet1").Cells(i, "A").Value = Worksheets("Sheet2").Cells(i, "A").Value Then

            If (Abs(Worksheets("Sheet1").Cells(i, "I").Value - Worksheets("Sheet2").Cells(i, "I").Value) * 86400) <= TimeValue("00:20:00 AM") Then
                                                                                                                           '^ valid time value "00:20:00 AM"          
                Worksheets("Sheet1").Cells(i, "K").Value = Worksheets("Sheet2").Cells(i, "I").Value

            Else

                Worksheets("Sheet1").Cells(i, "K").Value = "Check"

            End If

        End If

    Next i

End Sub

Type mismatch error VBA

1 Answers