Run-Time Error '1004'; ?

[NYRANGERS423]

So I get this when I use my spreadsheet, Rint-Time error '1004'; Application-definded or object-defined error.

Here is the code I have,
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Columns("A")) Is Nothing Then
If Target.Row > 1 And Target.Offset(, 2) = "" Then
Target.Offset(, 2) = Date
Target.Offset(, 6) = Time
**Target.Offset(, 8) = Worksheets("Name List").Range("E2").Value
End If
ElseIf Not Intersect(Target, Columns("B")) Is Nothing Then
If Target.Row > 1 And Target.Offset(, 6) = "" And Target.Offset(, 1) <> " " Then
Target.Offset(, 6) = Time
Target.Offset(, 9) = Worksheets("Name List").Range("E2").Value
End If
End If
End If
End Sub

**The Star indicates where the error occurs.
What I don't get is I have the same line of code a little farther down "Target.Offset(, 9) = Worksheets("Name List").Range("E2").Value" and it works just fine. Is excel not able to find the cell?

 

[Joe4]

When using the Worksheet_Change event procedure, if you are updating cell values within it, it is best to disable events while those updates happen so the event procedure doesn't call itself and get caught in an endless loop.

Also, VBA will only show you the first error it encounters (so there may be others that you many not see until you fix the first one).

So give those code a try (and use Code tags when posting your code, especially when nesting IF statements -- it makes it much easier to read!)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count = 1 Then
        If Not Intersect(Target, Columns("A")) Is Nothing Then
            If Target.Row > 1 And Target.Offset(, 2) = "" Then
                Application.EnableEvents = False
                Target.Offset(, 2) = Date
                Target.Offset(, 6) = Time
                Target.Offset(, 8) = Worksheets("Name List").Range("E2").Value
                Application.EnableEvents = True
            End If
        ElseIf Not Intersect(Target, Columns("B")) Is Nothing Then
            If Target.Row > 1 And Target.Offset(, 6) = "" And Target.Offset(, 1) <> " " Then
                Application.EnableEvents = False
                Target.Offset(, 6) = Time
                Target.Offset(, 9) = Worksheets("Name List").Range("E2").Value
                Application.EnableEvents = True
            End If
        End If
    End If
    
End Sub

 

[NYRANGERS423]

When using the Worksheet_Change event procedure, if you are updating cell values within it, it is best to disable events while those updates happen so the event procedure doesn't call itself and get caught in an endless loop.

Also, VBA will only show you the first error it encounters (so there may be others that you many not see until you fix the first one).

So give those code a try (and use Code tags when posting your code, especially when nesting IF statements -- it makes it much easier to read!)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count = 1 Then
        If Not Intersect(Target, Columns("A")) Is Nothing Then
            If Target.Row > 1 And Target.Offset(, 2) = "" Then
                Application.EnableEvents = False
                Target.Offset(, 2) = Date
                Target.Offset(, 6) = Time
                Target.Offset(, 8) = Worksheets("Name List").Range("E2").Value
                Application.EnableEvents = True
            End If
        ElseIf Not Intersect(Target, Columns("B")) Is Nothing Then
            If Target.Row > 1 And Target.Offset(, 6) = "" And Target.Offset(, 1) <> " " Then
                Application.EnableEvents = False
                Target.Offset(, 6) = Time
                Target.Offset(, 9) = Worksheets("Name List").Range("E2").Value
                Application.EnableEvents = True
            End If
        End If
    End If
    
End Sub

Ok I tried the code and It did and didn't work. Another error occurred but It said the cell I was writing to was protected. HAHA I forgot to unchecked the locked in protection table for that column. After I did that both your and my old code worked. I am going to use your cause it looks cleaner. HAHA thanks sooo much. I appreciate it.

 

[Joe4]

You're welcome.

I guess I will be rooting for your Rangers in the playoffs this year, as my Sabres won't be in playoffs yet again this year! Maybe next year...

 

[NYRANGERS423]

You're welcome.

I guess I will be rooting for your Rangers in the playoffs this year, as my Sabres won't be in playoffs yet again this year! Maybe next year...

HAHA good to know your on my side... for now!