Runtime Error 13- VBA

[bloodybrit90]

The following macro works for worksheet with approx. 20,000 rows, however when I use this on a workshhet with approx. 95,000 rows I receive runtime error 13 (type mismatch). Could this be written in a better way?...Any advise?

Sub Formatcorrectly()
Dim I As Object
Application.ScreenUpdating = False
For Each I In ActiveSheet.UsedRange
I = I.Value
Next I
Application.ScreenUpdating = True
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("AA2:AA" & Cells(Rows.Count, "AA").End(xlUp).Row)
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
c.NumberFormat = "mm/dd/yyyy"
Next
Application.ScreenUpdating = False
End Sub

 

[sergioMabres]

In what line do you get the error?

 

[bloodybrit90]

In what line do you get the error?

c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))

I just ran the macro on a smaller set of numbers and it worked.

 

[sergioMabres]

Probably in column AA in a row there is a cell with letters or spaces so Left(c.Value, 4) returns "ABCD" that is not a valid number.
What I do is when I get the error I will click debug and point the cursor on c.Value to see what does it hold, what is the value of c.Value
You can avoid errors by checking contents of c.Value before applying to DateSerial
Cheers
Sergio