VBA Code Not working when modified

[Closed]
Report
Posts
2
Registration date
Wednesday October 1, 2014
Status
Member
Last seen
October 1, 2014
-
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
-
Hello!

I have the following VBA Code on an Excel worksheet, which has been working fine up until today, when I modified it slightly (just changing Target.Offset(, 9) to Target.Offset(, 10), and it has now stopped working... Any idea why this might be please? I appreciate your help. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K2:K4000")) Is Nothing Then GoTo Next_Part
If Target.Count > 1 Then Exit Sub
If Target = "" Then
Target.Offset(, -8) = ""
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
Target.Offset(, -8) = Format(Date, "dd mmm yy")
.EnableEvents = True
.ScreenUpdating = True
End With

Next_Part:

If Intersect(Target, Range("L2:L4000")) Is Nothing Then GoTo Next_Part2
If Target.Count > 1 Then Exit Sub
If Target = "" Then
Target.Offset(, 9) = ""
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
Target.Offset(, 9).Value = Target.Offset(, 9).Value + 1
.EnableEvents = True
.ScreenUpdating = True
End With

Next_Part2:

If Intersect(Target, Range("L2:L4000")) Is Nothing Then GoTo Next_Part3
If Target.Count > 1 Then Exit Sub
If Target = "" Then
Target.Offset(, 10) = ""
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
Target.Offset(, 10).Value = Target.Offset(, 10).Value + 1
.EnableEvents = True
.ScreenUpdating = True
End With

Next_Part3:

If Intersect(Target, Range("L2:L4000")) Is Nothing Then GoTo Next_Part4
If Target.Count > 1 Then Exit Sub
If Target = "" Then
Target.Offset(, -6) = ""
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
Target.Offset(, -6) = Format(Date, "dd mmm yy")
.EnableEvents = True
.ScreenUpdating = True
End With


Next_Part4:

If Intersect(Target, Range("A2:A4000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then
Target.Offset(, 15) = ""
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
Target.Offset(, 15) = Format(Date, "dd mmm yy")
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub

1 reply

Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
Hi Cristina,

The code it self works.

What do you mean by "stopped working"?
Do you get an error, don't you get the desired result or nothing happens at all?

Could you post your workbook (careful with sensitive info) using a file sharing site like www.speedyshare.com or ge.tt and post back the download link.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!