VBA Code Not working when modified

Closed
CristinaSC Posts 1 Registration date Wednesday October 1, 2014 Status Member Last seen October 1, 2014 - Oct 1, 2014 at 07:30 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 7, 2014 at 11:08 AM
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
Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 7, 2014 at 11:08 AM
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
0