Pass values from a range object to an array

Bill_Kro

New Member
Joined
Oct 28, 2017
Messages
14
I have some code where the user selects cells. (the location and number of cells selected will vary)
The code creates a range object based on the selected cells and assigns the variable name: Selected_Cells
I want to pass the values from the Selected_Cells range object to an array but don't know how to do this.
Thanks,
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,305
First declare a variable as a Variant...

Code:
    Dim v As Variant
Then you can transfer the values from your Range object to the variable like this...


Code:
    v = Selected_Cells.Value

Note, since the values are being transferred from the worksheet, the variable is assigned a 1-based two dimensional array, even if the Range object consists of one column or row. So to refer to the first item of the first row...

Code:
 v(1,1)

Hope this helps!
 

Bill_Kro

New Member
Joined
Oct 28, 2017
Messages
14
Domenic,

Thanks, I think the last part about it being a 1-based two dimensional array is my problem.

I was going to take the array and use it as the Criteria in an autofilter.

If I assign the create the array

Code:
v = array("Apple", "Pear", "Cherry")

then when I use it in the autofilter
Code:
Criteria1:=v(),

Then the table filters properly

When I selected the cells with Apple, Pear and Cherry as their values
and then assign them to the range object Selected_Cells
Code:
v = Selected_Cells.value
...
Criteria1:=v()

Then the table only filters on the first value - Apple

When I look in the Locals window, the array values are different.
In the first case when I directly defined the array contents, the Type appears to be Variant(0 to 2) and the details are listed as Variant/String
When I tried to define the array from the range object, the Type says Variant/Variant(1 to 3, 1 to 1) and the details are listed as Variant (1 to 1)

Is there a way around this problem?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,780
You could loop the values into a 1 dimensional 0-based array

Code:
Dim arrValues() as String
Dim oneCell as Range, i as Long

ReDim arrValues(0 to Selected_Cells.Count - 1)

i = 0
For Each oneCell in Selected_Cells.Cells
    arrValue(i) = CStr(oneCell.Value)
    i = i + 1
Next oneCell
 

Bill_Kro

New Member
Joined
Oct 28, 2017
Messages
14

ADVERTISEMENT

Mike,
Thank you so much! It worked perfectly. It was driving me crazy that I couldn't figure it out. I'm so thankful.
Bill
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,305
It looks like v has been assigned a N-Row by 1-Column array, in which case you would need to first transpose it to one dimensional array...

Code:
v = Application.Transpose(v)

Then you could assign it to Criteria1...

Code:
Criteria1:=v
 
Last edited:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This post is very similar to your post here https://www.mrexcel.com/forum/excel-questions/1089446-pass-values-range-object-array.html

Perhaps you do not realize that when this kind of InputBox comes up the user does not enter information into the box itself but actually selects the range of cells manually as if the input box was not there. When you click "Ok" on the InputBox the range selected is put into an array. Again, you do not enter info into the InputBox.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,050
Messages
5,526,494
Members
409,704
Latest member
saialkesh

This Week's Hot Topics

Top