Set values in an Array
 

Is there a way to load an array with data from a worksheet? Here is
the code I have.

ActiveSheet.ListObjects("List12559").Range.AutoFil ter Field:=1,
Criteria1:= _
Array("103", "113", "123", "213", "220", "227", "231", "235",
"290", "500", _
"540", "900"), Operator:=xlFilterValues

These values are in A1-A12. I am hoping to make this more dynamic so
when I add a criteria I can just add it to the sheet and not to the
sheet and the VBA macro.

Any help woudl be appreciated,
Jay

Set values in an Array
 

Try the hereunder code.
The 2 Message Boxes prove that the 12 values are kept, each' in the
respective cell.
=====================
Sub Fill_One_Dimensional_Array()
Dim Rng As Range
Arr = Range("A1:A12")
MsgBox Arr(3, 1)
MsgBox Arr(5, 1)
End Sub
================
To make the list dynamically - check for the last filled cell in column "A":
LR = Cells(Rows.Count ,1).End(xlUP).Row
then use LR in the Array filling command:
Arr = Range("A1:A" & LR)
Micky


"jlclyde" wrote:

Is there a way to load an array with data from a worksheet? Here is
the code I have.

ActiveSheet.ListObjects("List12559").Range.AutoFil ter Field:=1,
Criteria1:= _
Array("103", "113", "123", "213", "220", "227", "231", "235",
"290", "500", _
"540", "900"), Operator:=xlFilterValues

These values are in A1-A12. I am hoping to make this more dynamic so
when I add a criteria I can just add it to the sheet and not to the
sheet and the VBA macro.

Any help woudl be appreciated,
Jay
.

Set values in an Array
 

I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
criterial:=myarr, operator:=xlfiltervalues



jlclyde wrote:

Is there a way to load an array with data from a worksheet? Here is
the code I have.

ActiveSheet.ListObjects("List12559").Range.AutoFil ter Field:=1,
Criteria1:= _
Array("103", "113", "123", "213", "220", "227", "231", "235",
"290", "500", _
"540", "900"), Operator:=xlFilterValues

These values are in A1-A12. I am hoping to make this more dynamic so
when I add a criteria I can just add it to the sheet and not to the
sheet and the VBA macro.

Any help woudl be appreciated,
Jay

--

Dave Peterson

Set values in an Array
 

On Dec 7, 12:01*pm, Dave Peterson wrote:
I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. I get subscript out of range. Any other
thoughts? I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Set values in an Array
 

On Dec 7, 12:51*pm, jlclyde wrote:
On Dec 7, 12:01*pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. *I get subscript out of range. *Any other
thoughts? *I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. That is why I was gettign the error.

Thanks for the help,
Jay

Set values in an Array
 

On Dec 7, 1:36*pm, jlclyde wrote:
On Dec 7, 12:51*pm, jlclyde wrote:





On Dec 7, 12:01*pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. *I get subscript out of range. *Any other
thoughts? *I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. *That is why I was gettign the error.

Thanks for the help,
Jay- Hide quoted text -

- Show quoted text -

Dave,
I was wrong again....Here is the code I ahve and it is sorting th
ecorrect list it just does not leave nay value on the screen. I am
not sure what is goign on here is my code.
Jay
Sub Macro3()
Dim MyArray As Variant
With Sheet4
MyArray = .Range("A5", .Cells(5, .Range("A5").End
(xlToRight).Column)).Value
End With

Sheet5.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:= _
MyArray, Operator:=xlFilterValues
End Sub

Set values in an Array
 

I think the problem is with this statement:

MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight).Column)).Value

This .cells(5,.range("A5").end(xltoright).column will result in a number. And
that isn't gonna be valid argument inside Range().

So maybe...

MyArray = .Range("A5:A" & .Cells(5, .Range("A5").End(xlToRight).Column)).Value
or
MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight))).Value

I like starting at the far right (or bottom) and working the way left (or up):

MyArray = .Range("A5", .Cells(5,.columns.count).end(xltoleft)).Value

But I thought your list was in A1:A12--not horizontal????

(Still untested--in either direction.)

jlclyde wrote:

On Dec 7, 1:36 pm, jlclyde wrote:
On Dec 7, 12:51 pm, jlclyde wrote:





On Dec 7, 12:01 pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. I get subscript out of range. Any other
thoughts? I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. That is why I was gettign the error.

Thanks for the help,
Jay- Hide quoted text -

- Show quoted text -

Dave,
I was wrong again....Here is the code I ahve and it is sorting th
ecorrect list it just does not leave nay value on the screen. I am
not sure what is goign on here is my code.
Jay
Sub Macro3()
Dim MyArray As Variant
With Sheet4
MyArray = .Range("A5", .Cells(5, .Range("A5").End
(xlToRight).Column)).Value
End With

Sheet5.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:= _
MyArray, Operator:=xlFilterValues
End Sub

--

Dave Peterson

Set values in an Array
 

On Dec 7, 2:50*pm, Dave Peterson wrote:
I think the problem is with this statement:

*MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight).Column)).Value

This .cells(5,.range("A5").end(xltoright).column will result in a number. *And
that isn't gonna be valid argument inside Range().

So maybe...

*MyArray = .Range("A5:A" & .Cells(5, .Range("A5").End(xlToRight).Column)).Value
or
*MyArray = .Range("A5", .Cells(5, .Range("A5").End(xlToRight))).Value

I like starting at the far right (or bottom) and working the way left (or up):

*MyArray = .Range("A5", .Cells(5,.columns.count).end(xltoleft)).Value

But I thought your list was in A1:A12--not horizontal????

(Still untested--in either direction.)





jlclyde wrote:

On Dec 7, 1:36 pm, jlclyde wrote:
On Dec 7, 12:51 pm, jlclyde wrote:

On Dec 7, 12:01 pm, Dave Peterson wrote: I didn't test this, so it may not work...

Dim myArr as variant

with worksheets("nameofsheetwithlist")
* myarr = .range("a1",.cells(.rows.count,"A").end(xlup)).val ue
end with

'then your code
activesheet.listobjects("list12559").range.autofil ter field:=1, _
* *criterial:=myarr, operator:=xlfiltervalues

Dave, This did not work. *I get subscript out of range. *Any other
thoughts? *I am sure this is possible and I have been playign with it
all morning to no avail.
Thanks,
Jay

Dave,
I was wrong.....Your code works fine when I am pointing at the right
list. *That is why I was gettign the error.

Thanks for the help,
Jay- Hide quoted text -

- Show quoted text -

Dave,
I was wrong again....Here is the code I ahve and it is sorting th
ecorrect list it just does not leave nay value on the screen. *I am
not sure what is goign on here is my code.
Jay
Sub Macro3()
* * Dim MyArray As Variant
* * With Sheet4
* * * * MyArray = .Range("A5", .Cells(5, .Range("A5").End
(xlToRight).Column)).Value
* * End With

* * Sheet5.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:= _
* * * * MyArray, Operator:=xlFilterValues
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,
If you get a moment to test it does not show any of the values in the
array. I had the data originally going down, but then I changed then
to be column headings on a seperate list. Sorry for any confusion.

Thanks,
Jay

Set values in an Array
 

I started up xl2007.

I put some data into c7:J27.
I used Insert|Table to create the table (with headers). It defaulted to a name
of Table1.

I added some entries in C5:E5 that matched some of the values in the first
column of my table.

Then I ran this macro:

Option Explicit
Sub testme()

Dim myArr As Variant

With Worksheets("sheet1")

myArr = .Range("a5", .Cells(5, .Columns.Count).End(xlToLeft)).Value

.ListObjects("Table1").Range.AutoFilter Field:=1, _
Criteria1:=myArr, Operator:=xlFilterValues

End With

End Sub

If this doesn't help, please give more details--where the data is, where the
table is, and where that criteria is.






jlclyde wrote:

<<snipped
Dave,
If you get a moment to test it does not show any of the values in the
array. I had the data originally going down, but then I changed then
to be column headings on a seperate list. Sorry for any confusion.

Thanks,
Jay

--

Dave Peterson

Set values in an Array
 

Ps. Ron de Bruin has lots of vba samples for working with lists and tables
he
http://www.rondebruin.nl/tips.htm
look for Table

jlclyde wrote:
<<snipped
--

Dave Peterson