PDA

View Full Version : Runtime 1004 error



Mike_B
05-17-2016, 08:23 AM
Hi,

I have the below code in my worksheets which reads when a change is made to a cell in column L and outputs the date & time and the username to columns to the right. This works fine, however any time I paste in additional rows or delete a row, I get a runtime 1004 error, highlighting the Target.Offset rows as being problematic.

Any help would be appreciated!


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("L32:L5000")) Is Nothing Then Exit Sub
Target.Offset(0, 5) = Now()
Target.Offset(0, 6) = UserName()

End Sub

SamT
05-17-2016, 08:32 AM
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Or Target.Row < 32 Or Target.Column <> 12 Then Exit Sub
Target.Offset(0, 5) = Now()
Target.Offset(0, 6) = UserName()

End Sub

Mike_B
05-17-2016, 08:41 AM
That fixes the runtime error, however now if I change multiple cells within column L at once, Now() and UserName() aren't called. It only works if I amend the cells in column L one at a time. Any ideas?

Mike_B
05-17-2016, 09:00 AM
I suppose I can set Target.Count to a larger value.

SamT
05-17-2016, 02:54 PM
I've never had to do this but try changing just Target.Count to Target.Columns.Count

Aflatoon
05-18-2016, 12:49 AM
You should also really disable events if you're going to change cells in a change event.