Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range inside Array not working
With macro recorder I generated this code
Sub Macro6() ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _ Criteria1:=Array("1", "2"), Operator:=xlFilterValues End Sub My objetive is to replace the array values with my named range, so I do this: In cell A1 I write value 1 In cell A2 I write value 2 I create a named range called "CR", wich refers to range("A1:A2") Then I modify my macro to this, to include the named range inside the array: Sub Macro6() ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _ Criteria1:=Array(Range("CR")), Operator:=xlFilterValues End Sub The problem is that it doesn´t work...What I am doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range inside Array not working
What happens if you remove the array()
and just do it as follows?: Sub Macro6() ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _ Criteria1:=Range("CR"), Operator:=xlFilterValues End Sub "RCL" wrote in message ups.com: With macro recorder I generated this code Sub Macro6() ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _ Criteria1:=Array("1", "2"), Operator:=xlFilterValues End Sub My objetive is to replace the array values with my named range, so I do this: In cell A1 I write value 1 In cell A2 I write value 2 I create a named range called "CR", wich refers to range("A1:A2") Then I modify my macro to this, to include the named range inside the array: Sub Macro6() ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _ Criteria1:=Array(Range("CR")), Operator:=xlFilterValues End Sub The problem is that it doesn´t work...What I am doing wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range inside Array not working
That only filters the 2's for me.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RCL" wrote in message ups.com... With macro recorder I generated this code Sub Macro6() ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _ Criteria1:=Array("1", "2"), Operator:=xlFilterValues End Sub My objetive is to replace the array values with my named range, so I do this: In cell A1 I write value 1 In cell A2 I write value 2 I create a named range called "CR", wich refers to range("A1:A2") Then I modify my macro to this, to include the named range inside the array: Sub Macro6() ActiveSheet.Range("$A$1:$I$5004").AutoFilter Field:=1, _ Criteria1:=Array(Range("CR")), Operator:=xlFilterValues End Sub The problem is that it doesn´t work...What I am doing wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range inside Array not working
Hi JMay, If I use:
Criteria1:=Range("CR") my Excel crashes ! Doing more research, I have discovered that my array must be in the same row , so I changed my range CR, which now refers to A1 and B1. Doing this I don´t get the error, but the filter doesn´t show anything... So, I still need help on how to convert Criteria1:=Array("1", "2") TO Criteria1:=Range("A1:B1") or Range("CR") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range inside Array not working
What is in your Range A1:B1 (Values?) << Also, What Sheet name is it
in? What is the sheetname of your autofitered data? "RCL" wrote in message oups.com: Hi JMay, If I use: Criteria1:=Range("CR") my Excel crashes ! Doing more research, I have discovered that my array must be in the same row , so I changed my range CR, which now refers to A1 and B1. Doing this I don´t get the error, but the filter doesn´t show anything... So, I still need help on how to convert Criteria1:=Array("1", "2") TO Criteria1:=Range("A1:B1") or Range("CR") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range inside Array not working
- My Data Table is located in sheet2
- It has a first column with month numbers (1,2, 3, 4...) In sheet 4 I have - Value 1 in A1 - Value 2 in B1 - Named Range "CR" refers to Range("A1:B1") (it is a public named range) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range inside Array not working
Change to Suit: (This worked for me) << on a smaller scale
Sub Macro7() ActiveSheet.Range("$A$1:$E$10").AutoFilter Field:=1, _ Criteria1:=Range("CR")(1), Operator:=xlOr, _ Criteria2:=Range("CR")(2) End Sub Jim May "RCL" wrote in message oups.com: - My Data Table is located in sheet2 - It has a first column with month numbers (1,2, 3, 4...) In sheet 4 I have - Value 1 in A1 - Value 2 in B1 - Named Range "CR" refers to Range("A1:B1") (it is a public named range) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Question about working with range array | Excel Programming | |||
Assigning range to array not working | Excel Programming | |||
Assigning array to range inside Excel Object | Excel Programming | |||
Check if a String is inside an Array | Excel Programming |