Type Mismatch error

[tjiddy89]

Hello all! I'm running into a runtime error '13' type mismatch error with the below code. The code is supposed to delete any row with a date older than two years. Initially I did not have the Int() formula, but for fear that the cell value was not an integer I put it in. Any suggestions?

' lRow is a predefined variable that finds the last row with a value
Worksheets(1).Range("E4:E" & lRow).Select
For Each cell In Selection
  If (Int(Year(cell.Value)) > Int((Year(Now) - 2))) Then
    cell.EntireRow.Delete
  End If
Next cell

An example of what is stored in E column is

22-04-16 05:49

<tbody>
</tbody>

Thank you in advance for your help.

 

[Joe4]

You do not need to use the INT function on top of the YEAR, as the YEAR function returns just the year portion of the date, so it will never contain a decimal.

If you are getting errors on certain cells, it is probably because your do not have a valid date entry in a certain cell.

 

[tjiddy89]

Thank you very much for your response, Joe. I've changed the format of the referenced cells to Date in Excel, but it's still giving me the same error...any suggestions on how to proceed?

What is even more weird is if I change the script within the if statement to, say, cell.Font.Color=vbRed, run it, and then switch it back - it works just fine for one iteration and works perfectly...but then fails again with the mismatch error again.

 

[AlphaFrog]

Try this...

[COLOR=green]' lRow is a predefined variable that finds the last row with a value[/COLOR]
[COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], d [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
d = [COLOR=darkblue]Date[/COLOR]Add("yyyy", -2, Date)  [COLOR=green]'two years before today's date[/COLOR]
[COLOR=darkblue]For[/COLOR] i = lRow [COLOR=darkblue]To[/COLOR] 4 [COLOR=darkblue]Step[/COLOR] -1
    [COLOR=darkblue]With[/COLOR] Worksheets(1).Range("E" & i)
        [COLOR=darkblue]If[/COLOR] IsDate(.Value) [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] .Value < d [COLOR=darkblue]Then[/COLOR] .EntireRow.Delete
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]Next[/COLOR] i

Your original code was comparing only the years within the dates, which is not necessarily a 2 year date difference e.g.; Dec 31, 2016 is not two years difference from Jan 1, 2018 even though their year values are two years apart.

Your original code deleted rows as you looped from the top down. This practice could cause some rows not to be tested as they shift up to fill a deleted row. The new code loops from the bottom up.

 

[tjiddy89]

Try this...

[COLOR=green]' lRow is a predefined variable that finds the last row with a value[/COLOR]
[COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], d [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
d = [COLOR=darkblue]Date[/COLOR]Add("yyyy", -2, Date)  [COLOR=green]'two years before today's date[/COLOR]
[COLOR=darkblue]For[/COLOR] i = lRow [COLOR=darkblue]To[/COLOR] 4 [COLOR=darkblue]Step[/COLOR] -1
    [COLOR=darkblue]With[/COLOR] Worksheets(1).Range("E" & i)
        [COLOR=darkblue]If[/COLOR] IsDate(.Value) [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] .Value < d [COLOR=darkblue]Then[/COLOR] .EntireRow.Delete
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]Next[/COLOR] i

Your original code was comparing only the years within the dates, which is not necessarily a 2 year date difference e.g.; Dec 31, 2016 is not two years difference from Jan 1, 2018 even though their year values are two years apart.

Your original code deleted rows as you looped from the top down. This practice could cause some rows not to be tested as they shift up to fill a deleted row. The new code loops from the bottom up.

This worked perfectly thank you very much Alpha! It's been a long day and you've definitely made it easier.