DateDiff gives Type mismatch error 13?

karinfromsweden

New Member
Joined
Oct 30, 2018
Messages
7
Hey all,
I have this neat little macro that runs on every start of a sheet. It's (supposed to be) pretty simple, checking for rows that are older than 180 days and then deleting part of the content of the row. My boss was so happy over this GDPR-compliant macro. :rolleyes:
But then a few days ago it started giving "run time error 13 Type mismatch".

I have doublechecked that the column only holds correctly formatted dates. And from what I can see, the macro works. So why does it give an error?
Any help would be dearly appriciated! Thank you!

Code:
Sub ClearCells()
Dim Cel As Range, Ws As Worksheet
Set Ws = Sheets("START")
For Each Cel In Ws.Range("A5", Ws.Range("A" & Rows.Count).End(xlUp))
    If DateDiff("d", Cel, Date) > 180 And Cel.Offset(, 0) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
Next Cel
End Sub
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fennek

Active Member
Joined
Nov 21, 2016
Messages
278
Hi Karin, without any tests: Have you checked, that there are only dates in column "A"? regards
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,539
Office Version
365
Platform
Windows
When you get the error what is the value in Cel?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,635
Office Version
365
Platform
Windows
try running this to tell you which cells have got problems
Code:
Sub ClearCells()
    Dim Cel As Range, Ws As Worksheet, Msg As String
    Set Ws = Sheets("START")
    For Each Cel In Ws.Range("A5", Ws.Range("A" & Rows.Count).End(xlUp))
        On Error Resume Next
        If DateDiff("d", Cel, Date) > 180 And Cel.Offset(, 0) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
        If Err.Number > 0 Then Msg = Msg & vbCr & Cel.Address & vbTab & Cel.Value
        On Error GoTo 0
    Next Cel
    MsgBox Msg, vbCritical, "Problem cells"
End Sub
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,635
Office Version
365
Platform
Windows
Why use Cel.Offset(, 0) ?
Without any offset in any direction it is the same as Cel
 
Last edited:

karinfromsweden

New Member
Joined
Oct 30, 2018
Messages
7
try running this to tell you which cells have got problems
Code:
Sub ClearCells()
    Dim Cel As Range, Ws As Worksheet, Msg As String
    Set Ws = Sheets("START")
    For Each Cel In Ws.Range("A5", Ws.Range("A" & Rows.Count).End(xlUp))
        On Error Resume Next
        If DateDiff("d", Cel, Date) > 180 And Cel.Offset(, 0) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
        If Err.Number > 0 Then Msg = Msg & vbCr & Cel.Address & vbTab & Cel.Value
        On Error GoTo 0
    Next Cel
    MsgBox Msg, vbCritical, "Problem cells"
End Sub

Wow! This gave me an empty cell, but I did Clear > All and now it works. Weird. THANK YOU!
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,635
Office Version
365
Platform
Windows
Not all empty-looking cells are truly empty! ;)
Possibly contained an errant space
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,076
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top