Help re: VBA Macro Run-time error 13 mismatch and loops

[powerpivotlegal]

Hello,

This post is a follow-up to an earlier thread http://www.mrexcel.com/forum/excel-...ek-ending-date-override-cell.html#post4404190.

The working code below essentially overrides a user's data to input the correct week-ending date (Column H) and allows the user to hide row(s) by marking an 'X' in column J.

However, the macro gives out a run-time error 13, type mismatch when you delete a row from the formatted table or try to auto-fill (i.e. double-clicking or mass copy/paste) the week-ending date column.

My IT manager says I need a loop code or error handler, but I have no idea what code or parameters should be added.

This forum community has been amazing help to get me this far. Now I just need help eliminating the buggy issues.

Function dhLastDayInWeek(Optional dtmDate As Date = 0) As Date
' Returns the last day in the week specified by
' the date in dtmDate.
' Uses localized settings for the first day of the week.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
dhLastDayInWeek = dtmDate - Weekday(dtmDate, vbUseSystem) + 7
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16384 Then
Exit Sub
End If

' If you update a cell in column H and the value is not blank then
If Not Intersect(Target, Range("H:H")) Is Nothing And Target.Value <> "" Then
' Disable events to prevent infinite loop issues
Application.EnableEvents = False
' Update the target value by calling the custom function and work out the last day of the week
Target.Value = dhLastDayInWeek(Target.Value)
' Re-enable events
Application.EnableEvents = True
End If

If Not Intersect(Target, Range("J:J")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "x" Or Target.Value = "X" Then
Rows(Target.Row).Hidden = True
End If
End If

End Sub


Many thanks!

 

[Joe4]

Please continue in your original thread.

All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).