Encountering VBA "TYPE MISMATCH" ERROR

Question

I am coding a VBA project whereby I stored a column of file directory in a hidden cell and the system will loop through these cells one by one then go into the absolute directory path to check if exist. If the file exist, the checkbox is checked and a timestamp will appear. Then if the timestamp is more than the deadline, the timestamp will change to red font color. However I am encountering the above problem which I suspect it's my date format but I tried but am still lost on how to edit my code. Hence I seek your gracious to help me see what i can do. Thanks.

Sub test5()
Dim MyFile As String
Dim FinalRow As Long
Dim Row As Long
Dim i As Integer
Dim d As Integer
d = 3
i = 1
FinalRow = Range("A65536").End(xlUp).Row
For Row = 1 To FinalRow
     If Not IsEmpty(ActiveSheet.Cells(Row, "S")) Then
            MyFile = ActiveSheet.Cells(Row, "S").Value
            If Dir(MyFile) <> "" Then
                ActiveSheet.OLEObjects("CheckBox" & i). _
                Object.Value = True
                ActiveSheet.Cells(d, "F").Value = Format(Now, "dd-mm-yy")
                If (ActiveSheet.Cells(d, "F") - ActiveSheet.Cells(d, "G") >= 0) Then
                    ActiveSheet.Cells(d, "F").Font.Color = vbRed
                End If
                i = i + 1
                d = d + 1
            End If
     End If
Next
End Sub

Thanks a lot all, I have resolved the issue. Instead I have added a MonthView object into the userform to simplify things. So its like a calendar pop up where user can choose their date. But now I encounter another problem. After I resolve the date issue, my check file loop can't work. It can check the first checkbox after which any subsequent files cannot be found or looped even though the file exist. Does it have anything with the object or is just my algorithm logic?

Answer 1

Thanks Everyone. I solved. Below is the code. Credit goes to Patrick. Thanks a million.

Sub test5()
    Dim MyFile As String
    Dim FinalRow As Long
    Dim Row As Long
    Dim i As Integer
    Dim d As Integer
    d = 2
    i = 0
    FinalRow = Cells(Rows.count, "S").End(xlUp).Row
    For Row = 3 To FinalRow
         If Not IsEmpty(ActiveSheet.Cells(Row, "S")) Then
                i = i + 1
                d = d + 1
                MyFile = ActiveSheet.Cells(Row, "S").Value
                If Dir(MyFile) <> "" Then
                    ActiveSheet.OLEObjects("CheckBox" & i). _
                    Object.Value = True
                    With ActiveSheet.Cells(d, "F")
                        .Value = Now
                        .NumberFormat = "dd-mm-yy"
                        'If (ActiveSheet.Cells(d, "F") - ActiveSheet.Cells(d, "G") >= 0) Then
                        '    ActiveSheet.Cells(d, "F").Font.Color = vbRed
                        'End If
                        If (.Value - .Offset(0, 1).Value) >= 0 Then
                            .Font.Color = vbRed
                        Else
                            .Font.Color = vbBlack
                        End If
                    End With
                   ' i = i + 1
                    'd = d + 1
                End If
         End If
    Next
End Sub

But is there a way to code to bypass error 400?

Answer 2

Your ActiveSheet.Cells(d, "F").Value = Format(Now, "dd-mm-yy") has turned ActiveSheet.Cells(d, "F") into a text, so it cannot do arithmetic.

You should use ActiveSheet.Cells(d, "F").Value = Now and change it's NumberFormat to "dd-mm-yy".

Also you should undo changing the color from Red to Black (or default color) if it doesn't fall in IF F > G.

Try below:

Sub test6()
    Dim MyFile As String
    Dim FinalRow As Long
    Dim Row As Long
    Dim i As Integer
    Dim d As Integer
    d = 3
    i = 1
    FinalRow = Cells(Rows.Count, "A").End(xlUp).Row
    For Row = 1 To FinalRow
         If Not IsEmpty(ActiveSheet.Cells(Row, "S")) Then
                MyFile = ActiveSheet.Cells(Row, "S").Value
                If Dir(MyFile) <> "" Then
                    ActiveSheet.OLEObjects("CheckBox" & i). _
                    Object.Value = True
                    With ActiveSheet.Cells(d, "F")
                        .Value = Now
                        .NumberFormat = "dd-mm-yy"
                        'If (ActiveSheet.Cells(d, "F") - ActiveSheet.Cells(d, "G") >= 0) Then
                        '    ActiveSheet.Cells(d, "F").Font.Color = vbRed
                        'End If
                        If (.Value - .Offset(0, 1).Value) >= 0 Then
                            .Font.Color = vbRed
                        Else
                            .Font.Color = vbBlack
                        End If
                    End With
                    i = i + 1
                    d = d + 1
                End If
         End If
    Next
End Sub

Answer 3

Something in either Cells(d, "F") or Cells(d, "G") is not a number - or at least, cannot be resolved to be a number; it could be that the value in one of the cells has no value, or is something textual.