Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as argument in filter range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as argument in filter range
if
Dim rng as Range set rng = Range("A1:C119") or set rng = Range("A1"C" & x) then Range(rng) is illegal syntax so Range(Rng).AdvancedFilter should be Rng.AdvancedFilter -- Regards, Tom Ogilvy "Bert" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as argument in filter range
I'm still getting the error message.
I know I've gotten the rng defined correctly (thanks) because it works correctly with rng.Select but rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("I2:I3"), CopyToRange:=Range("M1:O1"), Unique:=False is still not working. ("Run-time error 1004. The extract range has a missing or illegal field name.") Bert "Tom Ogilvy" wrote in message ... if Dim rng as Range set rng = Range("A1:C119") or set rng = Range("A1"C" & x) then Range(rng) is illegal syntax so Range(Rng).AdvancedFilter should be Rng.AdvancedFilter -- Regards, Tom Ogilvy "Bert" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as argument in filter range
The extract range has a
missing or illegal field name.") so Range("M1:O1") contains something other than a column name from the data source. If that doesn't appear clear or to agree with what is there, then for your initial test, change Range("M1:O1") to Range("M1") and make sure that cell and the ones to the right are blank. That should get it working. Then you can try getting more sophisticated. -- Regards, Tom Ogilvy "Bert" wrote in message ... I'm still getting the error message. I know I've gotten the rng defined correctly (thanks) because it works correctly with rng.Select but rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("I2:I3"), CopyToRange:=Range("M1:O1"), Unique:=False is still not working. ("Run-time error 1004. The extract range has a missing or illegal field name.") Bert "Tom Ogilvy" wrote in message ... if Dim rng as Range set rng = Range("A1:C119") or set rng = Range("A1"C" & x) then Range(rng) is illegal syntax so Range(Rng).AdvancedFilter should be Rng.AdvancedFilter -- Regards, Tom Ogilvy "Bert" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average: 2nd argument as a variable in another cell av(c6:c(a3)) | Excel Discussion (Misc queries) | |||
Range as argument in function | Excel Programming | |||
type variable as argument of a sub | Excel Programming | |||
Passing range as argument | Excel Programming | |||
passing a variable as an argument to a function | Excel Programming |