VBA Code Not working when modified

Closed
CristinaSC
Posts
2
Registration date
Wednesday October 1, 2014
Status
Member
Last seen
October 1, 2014
- Oct 1, 2014 at 07:30 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 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

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
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