Run-time error 13 - Type mismatch

[skyking]

Dear all,

Can you help to fix the Run-time error for the VBA?

When I step into the VBA code and it pops up a message box at the red line below with "Run-time error 13 - Type mismatch". Can you help?

Private Sub CommandNextMonth_Click()
Dim val As String
Dim LR As Range
Dim a As Range
Dim val2 As Date
Cells(72, "H") = Date 'Set Last Access Day to Current Day
val = Cells(72, "J") 'Set to End Day (Last Month)
Debug.Print val
val2 = CDate(val) 'Change val to calculable
Debug.Print val2
For Each a In Range("A53:A100")
Debug.Print a.Address
Set a = ActiveWorkbook.Sheets(1).Range("A53:A53")
If a = "" Then Exit For
If a.Value < val2 Then
Debug.Print a.Address, a.Value, val2
Application.GoTo a, True
a.EntireRow.Delete xlNone
End If
Next
CommandNextMonth.Enabled = False
CommandPreviousMonth.Enabled = True
Application.Calculate
End Sub

Your early reponse is much appreciate.

Regards,
Wky King

 

[Dryver14]

Ive just run this and dont seem to get a problem if there is a date in J72
what do you have there?

 

[skyking]

As it mention at the end of the red line.
J72 should be the end day of previous month (eg. 31/03/2011).
Will you please input it to J72 and re-run the code to get same result?

 

[Dryver14]

so you are looking for that line of code to generate the last day of the month prior to H72?

 

[skyking]

No, I suppose to get the cell value to "val".
(ie val = Cells(72, "J").Content OR val = Cells(72, "J").value)
That means val should be 31/3/2011.

 

[Marcelo Branco]

Hi,

With J72 = 31/03/2011 it worked perfect for me also.

M.

 

[Marcelo Branco]

Complementing previous post

Immediate Window
31/03/2011
31/03/2011
$A$53

M.

 

[skyking]

Marcelo,

I don't understand what you mean by "With J72 = 31/03/2011 it worked perfect for me also".
Did you copy my code and run it yourself with J72 = 31/03/2011?
Anyway, I still get that run-time error 13: Type mismatch message box pops up.

 

[Marcelo Branco]

Marcelo,

I don't understand what you mean by "With J72 = 31/03/2011 it worked perfect for me also".
Did you copy my code and run it yourself with J72 = 31/03/2011?
Anyway, I still get that run-time error 13: Type mismatch message box pops up.

I just typed 31/03/2011 in J72 and run the macro.

Yes, i have just copied your code, exactly as it is, and it worked fine for me. No error.

M.

 

[ogo]

I believe that you are getting the error when the cell J72 is empty. If the J72 is having some date , the code will work fine. If not can you check what is the value in J72, when the error is thrown?

Thanks,

Ogo