VBA Error 1004 application defined or object defined error

joey peanuts

New Member
Joined
Mar 20, 2011
Messages
18
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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).

Rich (BB code):
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

New Member
Joined
Mar 20, 2011
Messages
18
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

Legend
Joined
Jun 19, 2002
Messages
63,650
You are welcome. You could save a couple of lines of code using

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,711
Messages
5,524,436
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top