2

I have two different sheets and i have to assign 2 ranges from these two sheets to two different array but the problem is i can not specify a sheet before range for e.g 

Dim flArr() as variant 
flArr = Sheets("xxx").range(A1:A10)

This gives me an error. any workaround?

Thanks

Shan
  • 429
  • 3
  • 14
  • 31

1 Answers1

3

Look at the differences between your code and mine. Remove the parenthesis after your variant variable, let excel figure out it is an array. Also you need to include the range in quotations and specify that you want the values from the range.

Dim flArr As Variant
flArr = Sheets("xxx").Range("A1:A10").Value

Hope this helps :)

You could also do some less common construction as this, but it will be just awkward.

Dim arr() As Variant
ReDim arr(1 To 10)
arr() = Sheets("xxx").Range("A1:A10").Value

Hope this helps

  • thanks..but what is the difference with brackets and without. – Shan Nov 01 '15 at 19:09
  • the difference is that with the brackets Excel creates a variable to store and array if you want to write to it you will need to have the correct amount of indexes. The way without the brackets you will expand the indexes as needed. –  Nov 01 '15 at 20:42