VBA Error 1004 application defined or object defined error

[joey peanuts]

Excel 2007
This seems so simple - there must be something obvious I am missing.


Private Sub Worksheet_Activate()
Dim myCounter As Integer
myCounter = 9
If Range("A9").Value = "" Then
For Each cell In Worksheets("TAKE OFF").Range("VND_CHOSEN").Cells
If cell.Value = "Renewal" Then
If cell.Offset(0, 1).Value = "Series1" Then
Worksheets("RBA_RECT").Cells(myCounter, 1).Value = cell.Offset(0, -3).Value
myCounter = myCounter + 1
End If
End If
Next cell
End If
End Sub

The error shows up on the underlined line. I have test for cell.offset(0,-3). value. It returns the correct value.

Thank you

 

[VoG]

What range does VND_CHOSEN cover?

 

[joey peanuts]

What range does VND_CHOSEN cover?

D13:D68 of the worksheet "Take Off"

 

[VoG]

I've tested that and it works for me with no error. The only thing I changed was a declaration of cell type but I doubt that has any bearing on it. Are any of the sheets protected (though I think that would cause a different error).

Private Sub Worksheet_Activate()
Dim myCounter As Integer, cell As Range
myCounter = 9
If Range("A9").Value = "" Then
    For Each cell In Worksheets("TAKE OFF").Range("VND_CHOSEN").Cells
        If cell.Value = "Renewal" Then
            If cell.Offset(0, 1).Value = "Series1" Then
                Worksheets("RBA_RECT").Cells(myCounter, 1).Value = cell.Offset(0, -3).Value
                myCounter = myCounter + 1
            End If
        End If
    Next cell
End If
End Sub

 

[joey peanuts]

It was the protection I had. I just added a unprotect before the assignment line and then a protect after and voila.

Thank you!

 

[VoG]

You are welcome. You could save a couple of lines of code using

Private Sub Worksheet_Activate()
Dim myCounter As Integer, cell As Range
myCounter = 9
If Range("A9").Value = "" Then
    For Each cell In Worksheets("TAKE OFF").Range("VND_CHOSEN").Cells
        If cell.Value = "Renewal" And cell.Offset(0, 1).Value = "Series1" Then
                Worksheets("RBA_RECT").Cells(myCounter, 1).Value = cell.Offset(0, -3).Value
                myCounter = myCounter + 1
        End If
    Next cell
End If
End Sub