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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
It was the protection I had. I just added a unprotect before the assignment line and then a protect after and voila.

Thank you!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,325
Messages
6,118,894
Members
448,854
Latest member
Eduard_Stoo

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
Back
Top