Range to Array. Why doesn't this line work? 1

[davedave24]

I'm trying to put a range (column B in a worksheet) into an array when the form loads. All I require is that it reads down column B until there is an empty cell, and adds those values to the array rngBags. This only returns the first value though (I'm only just learning arrays so bear with me)

'at the top
Dim rngBags as Variant

'in userform_intialize
Set rngBags = Worksheets("Customers").Range("B1").End(xlDown)

Do I need to use ReDim or something?

 

[SkipVought]

Hi,

Your code defines ONE cell.

With Worksheets("Customers")
   Set rngBags = Range(.Range("B1"), .Range("B1").End(xlDown))
end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[davedave24]

Thank you Skip.

 

[DougAJ4]

Or to put the values into a variant array, rather than a range object:

With Worksheets("Customers")   
rngBags = Range(.Range("B1"), .Range("B1").End(xlDown)).Value2
end with

Doug Jenkins

http://newtonexcelbach.wordpress.com/