Runtime Error 1004

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 03 Mar 2008 20:14 GMT

I am receiving the following error for the code listed below:
'Run-time error 1004'
'Application-defined or object-defined error'

'Sub filter()

Sheets("RecordOfRoundsDetailed").Range("AllDetailedRecords"). _
AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("RecordOfRoundsDetailed").Range("DetailedFilterCriteria"),
_
CopyToRange:=Sheets("HomeDetailed").Range("DetailedFilterDestination"),
_
Unique:=False

End Sub'

Names are as follows:-
AllDetailedRecords =
"=OFFSET(RecordOfRoundsDetailed!$A$52,0,0,COUNTA(RecordOfRoundsDetailed!$A$52:$A$65536),221)"

DetailedFilterCriteria = "=RecordOfRoundsDetailed!$A$1:$E$2"

DetailedFilterDestination = "=OFFSET(HomeDetailed!$A$52,0,0,1,221)"

When I compile the code it is ok.

Is there any way to establish which part of the code is causing the error or
is there any obvious problem with the code?

Sandy

Reply to this Message

Sandy - 04 Mar 2008 12:19 GMT

Update

Ok, I changed my names as follows:-

Names:-

AllRecordsDetailed:-
"=OFFSET(RecordOfRoundsDetailed!$A$52,0,0,COUNTA(RecordOfRoundsDetailed!$A$52:$A$65536),221)"

FilterCriteriaDetailed:-
"=RecordOfRoundsDetailed!$A$1:$E$2"

FilterDestinationDetailed:-
"=OFFSET(HomeDetailed!$A$52,0,0,1,221)

and changed the code as follows (note this involved only changing the
"Names":-

Sub filter()

Sheets("RecordOfRoundsDetailed").Range("AllRecordsDetailed"). _
AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("RecordOfRoundsDetailed").Range("FilterCriteriaDetailed"),
_
CopyToRange:=Sheets("HomeDetailed").Range("FilterDestinationDetailed"),
_
Unique:=False

End Sub

Result - It now works as it should! I am curious though - could the problem
have been the names I was using??

Sandy

> I am receiving the following error for the code listed below:
> 'Run-time error 1004'
[quoted text clipped - 27 lines]
>
> Sandy

Reply to this Message

Ken Johnson - 04 Mar 2008 13:58 GMT

If COUNTA(RecordOfRoundsDetailed!$A$52:$A$65536) evaluates to zero
that is the error you will receive.

Ken Johnson

Reply to this Message

Sandy - 04 Mar 2008 14:05 GMT

It never evaluates to zero; before the filter is applied a record is
inserted.

I have a feeling that it was just a bit of corruption somewhere that was
corrected when I deleted the old names and then defined the new ones.

Sandy

> If COUNTA(RecordOfRoundsDetailed!$A$52:$A$65536) evaluates to zero
> that is the error you will receive.
>
> Ken Johnson

Reply to this Message

Dave Peterson - 04 Mar 2008 14:30 GMT

Maybe you could add a few lines like:

msgbox Sheets("RecordOfRoundsDetailed").Range("AllDetailedRecords") _
.address(external:=true)

To see if everything looks ok.

> I am receiving the following error for the code listed below:
> 'Run-time error 1004'
[quoted text clipped - 26 lines]
>
> Sandy

Signature

Dave Peterson

Reply to this Message

Sandy - 04 Mar 2008 15:51 GMT

Excellent suggestion Dave and having added the lines in I am happy that my
code is now doing as it should. Sadly I had already deleted all the original
Names so couldn't use your technique on that - useful for future use though.

Sandy

> Maybe you could add a few lines like:
>
[quoted text clipped - 37 lines]
>>
>> Sandy

Reply to this Message

Rate this thread: