Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

The extract range has a missing or illegal field name Results

Hi all -

Trying to pass an array to a procedure to use as criteria in advanced filer.
My code below is raising this error:
Run-time error '1004':
The extract range has a missing or illegal field name Debug points here:

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas what I did wrong
Thanks
-marc

	VB:
	
 btnOK_Click() 
    Dim arrValues() 
    Dim lngI As Long 
    Dim lngX As Long 
     
    With Me.lstAccounts 
        If .ListIndex  -1 Then 
            For lngI = 0 To .ListCount - 1 
                If .Selected(lngI) Then 
                    Redim Preserve arrValues(lngX) 
                    arrValues(lngX) = .List(lngI) 
                    lngX = lngX + 1 
                End If 
            Next lngI 
        End If 
    End With 
     
    comp_bs_FilterSelectResults arrValues(), lngX 
End Sub 
 
Private Sub comp_bs_FilterSelectResults(arrValues(), lngX As Long) 
    Dim wbBook As Workbook 
    Dim wsData As Worksheet 
    Dim wsExtract As Worksheet 
    Dim rngData As Range 
    Dim rngDest As Range 
    Dim rngCritData As Range 
    Dim rngCrit As Range 
    Dim lngRows As Long 
    Dim lngRows2 As Long 
     
    With Application 
        .ScreenUpdating = False 
        .Calculation = xlCalculationManual 
        .DisplayAlerts = False 
    End With 
     
    Set wbBook = ThisWorkbook 
    Set wsData = wbBook.Worksheets("Data") 
    Set wsExtract = wbBook.Worksheets("Extract") 
    lngRows = wsData.Range("A65536").End(xlUp).Row 
    Set rngData = wsData.Range("A1:S" & lngRows) 
    Set rngDest = wsExtract.Range("A1") 
     
     'Get criteria from array into range on ws
     'Begin at row 2, header is at row 1
     'Column 256
     
    wsData.Cells(1, 256).Value = "Descrip_3" 
    Set rngCritData = wsData.Range("IV2:IV" & lngX) 
    rngCritData = Application.Transpose(arrValues) 
    lngRows2 = wsData.Range("IV65536").End(xlUp).Row 
    Set rngCrit = wsData.Range("IV1:IV" & lngRows2) 
    wsExtract.UsedRange.Clear 
    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False 
    rngCrit.ClearContents 
     
     'Cleanup
     
    Set wbBook = Nothing 
    Set wsData = Nothing 
    Set wsExtract = Nothing 
    Set rngData = Nothing 
    Set rngDest = Nothing 
    Set rngCritData = Nothing 
    Set rngCrit = Nothing 
     
    With Application 
        .ScreenUpdating = True 
        .Calculation = xlCalculationAutomatic 
        .DisplayAlerts = True 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


XL03. I am a mushroom cloud laying ... and if Microsoft wants me to become a serial killer they're doing everything right. But before I die I'd like to understand the above error message, from advanced filter.

There are a lot of problems with advanced filter when copying to another worksheet. In the first place, well, it sort of doesn't work in the first place; but we trudge on, using the workaround of beginning the operation in the destination worksheet. However...there are numerous conditions that still need to be satisfied, otherwise

1. "The extract range has a missing or illegal field name"
2. "This command requires at least two rows of source data. You cannot use the command on a selection in only one row. Try the following:

If you're using an advanced filter, select a range of cells that contains at least two rows of data. Then click the Advanced Filter command again." (WTF is that about??)
3. "Microsoft Office Excel cannot determine which row in your list or selection contains column labels, which are required for this command"
4. The filter is performed, to the perfectly empty destination destsheet!A9, but only column A, and the filter column (D for me) comes across (sometimes I only get column A). B9:C999 are empty.

I have tried empty destination sheets, or copying the top row of the filter data to the destination sheet (even though I DO NOT NOT NOT want it there), or copying same by value, etc. etc. and never had any more success than item 4. above.

I think I've read every post imaginable on here, ozgrid, contextures, microsoft and newsgroups and never hear anything more than "the top of the destination range must match the top of the source." So if the destination is dest!A5 then I need a copy of the row ABOVE the source range data (the top row referred to in the filtering) (even if not meaningful) in A4:D4? What the $$$$ exactly is the holdup? What *exactly* does the destination sheet need for this to work??

Okay, try this on sheet Source:
D1 contains CITY
D2 contains CHI
row 3 is empty
A4 contains FOO
D4 contains CITY

A5 contains FOO
B5 contains lat
C5 contains long
D5 contains CHI

Line 6 is identical to line 5.
Line 7 is identical to line 5 except D7 is NY.

The advanced filter criteria is range D1:D2
The advanced filter database source is range Database, A4:D7

What for the love of God need I put on sheet Dest to get rows 5 and 6 fully shown on Dest?

I apologize for not spending another half hour logging all the anomolous results with the attempts for you. Considering that I've already spent 2.5 hours on a 2 minute task thanks to its lovely nondocumentation - well, I hope you will take pity of me, and any future victims that come across this...

Morning All,

I'm using the below code to paste unique values from column 'D' to cells 'A6001:A11000'.

I'm getting the following error message:

Run-Time error '1004'

The Extract Range Has A Missing Or Illegal Field Name

Can anybody see anything immediatley wrong?


	VB:
	
ActiveSheet.Range("D:D").Select 
Columns("D:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ 
"A6001:A11000"), Unique:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

I'm trying to use a procedure I found on the forum to copy and paste the top 10 values of a list according to a criteria. The procedure stops with the following error message: "The extract range has a missing or illegal field name"
It seems that it used to be a reported error in Excel 97, however, I'm running Excel 2002...

Here's the code:


	VB:
	
 RankFilter() 
    Dim rngTop As Range 
     
    Set rngTop = Sheets("Top10").[A1:K1] 
     'top 10 - sort & filter
    Range("Risks_Table").Sort Key1:=Range("J3"), _ 
    Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, _ 
    Orientation:=xlTopToBottom 
    Range("Risks_Table").AdvancedFilter Action:=xlFilterCopy, _ 
    CriteriaRange:=Sheets("Top10").Range("C1:C2"), CopyToRange:=rngTop, _ 
    Unique:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated,
Thanks in advance,

Hello all. I'm trying an advanced filter, copying unique records to another location. I'm stuck because I cannot comprehend what Excel wants. (It never actually tells you how to fix something, does it?) I simply receive this error message.

As far as I can see, it has a range of data (all kosher), a target range and the ability to give me unique records. So it's inability to achieve that is something I doubt I'll be able to figure out myself.

Any advice would be invaluable right now.

Thanks

I have been given a job to amend a rather large worksheet (not my own)
and I am having a rather frustrating time applying an advanced filter.
I am now ignoring the data and have created a much simpler filter on
limited data...but I still get the problem, i.e. if I enter the
following

(As the criteria I enter)

Year
past

(As the list I enter)

Year
old
past
present
next

And I ask the result to be copied to a cell below.

This works.

....but, if I just change the word "Year" to anything, say "Yang", I
get "The extract range has a missing or illegal field name". Why?

--
bridges_22
------------------------------------------------------------------------
bridges_22's Profile: http://www.excelforum.com/member.php...o&userid=31222
View this thread: http://www.excelforum.com/showthread...hreadid=508947

I am trying to use the Advanced Filter in a macro routine. If I include all
of the fields from the list, the macro works with my Copy to location, A1:K1.
If I change my Copy to location to exclude irrelevant columns, I change the
range to A1:G1.
Every time I do this, I get an error messge:
"The extract range has a missing or illegal field name."
Since the filter runs fine, if I include all the field names, I assume I
must have one missing.
I'm copying and pasting the values, so the Copy to field headings match the
list's field headings.

Here are the criteria, if that could have anything to do with it. Two
conditions on one column and one condition to compare two columns.
Course,Course,(No Header);
Room Rentals,Career
Days,=OR(Source!J2"",AND(Source!K2"",Source!K2 "Consumer"))

Thanks,

--
tj

I am trying to use the Advanced Filter in a macro routine. If I include all
of the fields from the list, the macro works with my Copy to location, A1:K1.
If I change my Copy to location to exclude irrelevant columns, I change the
range to A1:G1.
Every time I do this, I get an error messge:
"The extract range has a missing or illegal field name."
Since the filter runs fine, if I include all the field names, I assume I
must have one missing.
I'm copying and pasting the values, so the Copy to field headings match the
list's field headings.

Here are the criteria, if that could have anything to do with it. Two
conditions on one column and one condition to compare two columns.
Course,Course,(No Header);
<>Room Rentals,<>Career
Days,=OR(Source!J2<>"",AND(Source!K2<>"",Source!K2<>"Consumer"))

Thanks,

--
tj

I have been given a job to amend a rather large worksheet (not my own) and I am having a rather frustrating time applying an advanced filter. I am now ignoring the data and have created a much simpler filter on limited data...but I still get the problem, i.e. if I enter the following

(As the criteria I enter)

Year
past

(As the list I enter)

Year
old
past
present
next

And I ask the result to be copied to a cell below.

This works.

....but, if I just change the word "Year" to anything, say "Yang", I get "The extract range has a missing or illegal field name". Why?

Hi all

Receiving the above error message
I've checked things over several times and I'm not seeing what is causing the error.

Debug this line

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks
-marc

	VB:
	
 
 
Sub far_analysis_Commissions() 
    Dim wbBook As Workbook 
    Dim wsCommissions As Worksheet 
    Dim wsFData As Worksheet 
    Dim rngFData As Range 
    Dim rngCrit As Range 
    Dim rngCommissionsDest As Range 
    Dim rngCommissionsClear As Range 
    Dim arrCrit 
    Dim lngRowsFData As Long 
    Dim lngRowsCommissions As Long 
     
    With Application 
        .ScreenUpdating = False 
        .Calculation = xlCalculationManual 
        .DisplayAlerts = False 
    End With 
     
    Set wbBook = ThisWorkbook 
    Set wsFData = wbBook.Worksheets("Far_All_Data") 
    Set wsCommissions = Worksheets("Commissions") 
     
    lngRowsFData = wsFData.Range("A" & Rows.Count).End(xlUp).Row 
    lngRowsCommissions = wsCommissions.Range("A" & Rows.Count).End(xlUp).Row 
     
    Set rngFData = wsFData.Range("A12:I" & lngRowsFData) 
    Set rngCommissionsDest = wsCommissions.Range("A56") 
    Set rngCommissionsClear = wsCommissions.Range("A56:J" & lngRowsCommissions) 
     
    rngCommissionsClear.Clear 
     
    arrCrit = Array("Cat1", "S") 
    Set rngCrit = wsFData.Range("IV1:IV2") 
     
    rngCrit = WorksheetFunction.Transpose(arrCrit) 
     
    rngFData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngCommissionsDest, Unique:=False 
     
    rngCrit.ClearContents 
     
    With Application 
        .ScreenUpdating = True 
        .Calculation = xlCalculationAutomatic 
        .DisplayAlerts = True 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi....I have a worksheet called "ComplaintData" and a "Lookup" userform to search & display data from this worksheet. I have a code that did this just fine till yesterday and now is playing up.

I have changed something that has caused this and I cant figure out what it is.

Below is my code:


	VB:
	
 cmbFind_Click() 
    Set c = Range("a65536").End(xlUp).Offset(1, 0) 
     'write userform entries to database
    Dim DataSH As Worksheet 
    Set DataSH = Sheets("ComplaintData") 
    DataSH.Range("L1").Value = ComboBox1.Value 
    DataSH.Range("L2").Value = TextBox1.Text 
    DataSH.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("L1:L2") 
    ListBox1.RowSource = "ComplaintData!" & DataSH.Range("outdata").Address 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
from this code its highlighting the following line saying "Run time error 1004" - "The extract range has a mssing or illelgal fieldname":


	VB:
	
DataSH.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("L1:L2") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have data in columns A:K with heading from A1:K1.

I have created a dynamic name range in my worksheet called: "OutData"
which has following formula:


	VB:
	
=OFFSET(ComplaintData!$A$2,0,0COUNTA(ComplaintData!$A:$A)-1,10) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can someone help pleasE??? cheers

Sorry for not posting correctly. This is the first time I have ever posted on a forum.
I have attached a copy of the file I am working on along with the code I found in a previous thread. Not sure how to link to the thread.

I have a spreadsheet with 4 sheets (one for each region). Each sheet starts in cell A7 and goes to cell BV?. The ending cell can vary from month to month. I need to filter out the o/s amounts from each sheet to a mtd o/s sheet.

I found the sample 27991.xls on an earlier thread and I have been playing around with it to see if it would work. The sample works fine as long as I don't add an additional column to each of the sheets. I have changed the code to take into account the extra column but the macro stops with a "the extract range has a missing or illegal field name" after filtering the first sheet. I have also verified that all my titles are exactly the same on each sheet.

How would I go about editing the macro to take into account the extra columns?

I'm trying to use the following to do filtering of a list of changing
length.
x = LastRow() ' this is a function that returns the last row in the
current list
Rng = "$A$2:$C$" & Trim(Str(x)) ' this concatenates that number to a
variable to
Range("A2").Select ' this sets the selection at the top of the list.

' from what I can determine the following to lines should be identical. BUT
the one with the variable causes an error.
' while the second line works fine.

Range(Rng).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("I2:I3"), CopyToRange:=Range("M1:O1"), Unique:=False

Range("A1:C119").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("I2:I3"), CopyToRange:=Range("M1:O1"), Unique:=False

I've tried creating Rng to equal "$A$2:$C$119" and tried
Range(Cells(2,1),Cells(x,3)) all with the same error: "Run-time error 1004.
The extract range has a missing or illegal field name."

Any suggestions would be appreciated.
Thanks
Bert

I'm trying to extract data - using excel 2003 on a PC
I have set up my criteria area, list and extract area
I am using the filter, advanced filter trying to extract 3 or 4 things
I keep getting an error "The extract range has a missing or illegal field name"

how do I fix this???

Im having real problems with this macro I have got it to work on one table, then I have made a second macro to do the same sort of thing on another table, it works now and again then it comes up with a Run-time error '1004':
the extract range has a missing or illegal field names.
At the moment I have about 5 macros as I am just starting out once Ive finished Ill simplify it into 2 macros (1 to fill to data and 1 to delete data)
When I get the error if I clear the data above and filter it again I can run the second macro (QUESTIONS!AF37)

Hi, I am trying to use VBA to sort a list of company names into a unique list on the same sheet.
However, the code always gives me an error code of 'Run time error 1004. The extract range has a missing or illegal field name''.

My code is as follow:


	VB:
	
 colourcoding() 
     
    Dim foundcell, foundcell2, entrow, rng As Range 
    Dim coycol, coyrow, lastrow, i, j As Integer 
    Dim coylist(5000) As Variant 
     
    ThisWorkbook.Sheets("coy list").Range("A:Z").Clear 
    ThisWorkbook.Sheets("PCT Inorg").Range("A:IV").Interior.ColorIndex = 0 
     
    With ThisWorkbook.Sheets("PCT Inorg") 
         
        lastrow = .Cells(25500, 1).End(xlUp).Row 
        Set foundcell = .Cells.Find(what:="company name", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) 
         
        If Not foundcell Is Nothing Then 
            coycol = foundcell.Column 
            coyrow = foundcell.Row 
             
            For i = coyrow + 1 To lastrow 
                coylist(i) = .Cells(i, coycol) 
                ThisWorkbook.Sheets("coy list").Cells(i - 3, 1) = coylist(i) 
            Next i 
        End If 
    End With 
     
    With ThisWorkbook.Sheets("coy list") 
        Range("A1:A5000").Select 
        Range("A1:A5000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A1:A5000"), CopyToRange:=Range("B1"),
Unique:=True 
         
    End With 
     
    lastrowunique = ThisWorkbook.Sheets("coy list").Cells(25500, 2).End(xlUp).Row 
    j = 3 
    For i = 1 To lastrowunique 
         
        company = ThisWorkbook.Sheets("coy list").Cells(i, 2) 
         
        With ThisWorkbook.Sheets("PCT Inorg") 
             
            Set foundcell2 = .Cells.Find(what:=company, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) 
             
            If Not foundcell2 Is Nothing Then 
                firstadd = foundcell2.Address 
            End If 
             
            Do Until foundcell2 Is Nothing 
                 
                Set foundcell2 = .Cells.FindNext(after:=foundcell2) 
                coyrow2 = foundcell2.Row 
                 
                If j < 56 Then 
                    If Not foundcell2 Is Nothing Then 
                        Set entrow = ThisWorkbook.Sheets("PCT Inorg").Range("A" & coyrow2 & ":" & "H" & coyrow2) 
                        entrow.Interior.ColorIndex = j 
                    End If 
                Else 
                    j = 3 
                    If Not foundcell2 Is Nothing Then 
                        Set entrow = ThisWorkbook.Sheets("PCT Inorg").Range("A" & coyrow2 & ":" & "H" & coyrow2) 
                        entrow.Interior.ColorIndex = j 
                    End If 
                End If 
                 
                If foundcell2.Address = firstadd Then 
                    j = j + 1 
                    Exit Do 
                End If 
            Loop 
        End With 
    Next i 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I click on debug, the following line is highlighted in yellow.

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The only way to solve this is to go the sheet 'Coy list'. Click on any cell on this sheet and continue running the macro again (press F5).

This is how I solve this error. But how should I prevent this from happening?

Many thanks!

Hi everyone!

When I use Advanced Filter to filter out Unique records and copy them to a new location, it copies only the first record and only the first column of the data.

I tried with the heading-row included and excluded but the result doesn't change.

When I apply the same strategy to another worksheet with the same headings but different data, it reports an error "The extract range has a missing or illegal field name".

I think, I am not complying with some requirements of the feature.

Please help me ,in a bit detail, so as to get me out of this problem.

Hello, I cannot seem to get a simple advanced filter to work using VBA. It works fine when I perform the task manually on the same data range and criteria, but when I use the code below I get the message: " Run time error '1004' The extract range has a missing or illegal field name". My code is below:
Sheet12.Activate
Sheet12.Range("A1:R20000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("t1:t4"), CopyToRange:=Range("AA1:AR1"), Unique:=False
ActiveSheet.Range("AA1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Any help would be much appreciated.

Thx, Wratpack

I am very new to visual basic and am still attempting to understand the codes so please take this into account when replying to this post.
I am trying to use advanced filter to filter a list of names (which has multiple columns of data) and post the filtered results to individual worksheets based on the individual names. The module was copied from an working example created by D. Dalgleish (AdvFilterRepFiltered_example.xls). However, everytime I run the macro I get run-time error '1004' (the extract range has a missing or illegal field name) with the following piece of code highlighted

ws1.Columns("DC:DC").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("DA:DA"), Unique:=True

Hi, I have recorded a macro as follows:

Range("O5:X200").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A33:J35"), CopyToRange:=Range("A36:J36"), Unique:=False

but when I run it I get:

Runtime error 1004
The extract range has a missing or illegal field name

Can anyone plese help?


No luck finding an answer? You could always try Google.