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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,033
Messages
5,856,941
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top