VBA Timestamp/Run-time error 13

[bencharn]

Hello all,

First time posting, I'm having trouble with a simple VBA sub I've been working on. The idea is that the code drops a timestamp into a specific cell when the Plan-Do-Check-Accept (PDCA) status of that row changes.

Sub Worksheet_Change(ByVal Target As Range)
   Dim D As Range: Set D = Range("D:D")
   Dim v As String
    
   If Intersect(Target, D) Is Nothing Then
      Exit Sub
   End If
    
   Application.EnableEvents = False
      v = Target.Value
      If v = "P" Then Target.Offset(0, 1) = Now()
      If v = "D" Then Target.Offset(0, 2) = Now()
      If v = "C" Then Target.Offset(0, 3) = Now()
      If v = "A" Then Target.Offset(0, 4) = Now()
   Application.EnableEvents = True
   
End Sub

The code was running smoothly, however recent usage has been giving a Run-time error 13. I've worked the mismatch error is occuring between v (string) & Target.Value (variant), but I'm wondering what the easiest way is to fix this mismatch?

Thanks for your time!

Ben

 

[steve the fish]

When does it happen? When you clear cells? Or change more than one cell at once?

 

[bencharn]

Hi Steve, this happens as soon as I try and change the 'v' value. It also happens when I add additional rows to the table this code is working on.

When I debug the code, it highlights this line:

v = Target.Value

 

[steve the fish]

Have a try with this:

Dim rng As Range, c As Range

Set rng = Intersect(Target, Range("D:D"))

If Not rng Is Nothing Then
    For Each c In rng
        With c
            Select Case .Value
                Case "P": .Offset(0, 1) = Now()
                Case "D": .Offset(0, 2) = Now()
                Case "C": .Offset(0, 3) = Now()
                Case "A": .Offset(0, 4) = Now()
            End Select
        End With
    Next
End If

 

[bencharn]

That appears to have done the trick!

Thanks very much for your help