excel advancedfilter blowing up with a "1004" error

[jjfjr]

Hi;

I have a workbook with two sheets: Data and Results. My search button on
the results sheet when pressed will see what criteria has been entered in the
criteria section of Results and then display matches in Data in the Result
section of the Result sheet. I have the following declarations:

Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String
Dim MyRow As Integer, LastDataRow As Integer, DataRng As String
Dim CritRow As Integer, CritRng As String, RightCol As Integer
Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer

CritRng = "B3:I5" ' range of cells for Criteria table
ResultsRng = "B8:I8" ' range of headers for Results table

Towards the end of the code is the following line:

Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _
Unique:=False

When it tries to execute, I get the following error:

Run-time error '1004':
The extract range has a missing or illegal field name.

Is this extract range in the Results sheet (criteria or results section?) or
the Data sheet? Any help is appreciated.

 

[PHV]

What is the value of DataRng ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjfjr]

DataRng = "A2:H2" ' range of column headers for Data table

 

[Gavona]

Whenever I have had that error it has been a problem on the data sheet. Often a hidden column with no heading at all.

Create the values in ResultsRng by copying and pasting - guarantees no errors.
I can't recall if a duplicated heading causes problems - logically I would expect it to.

Thanks,

Gavin