Baffled with assignment of range to array why won't it work??? Anyhelp appreciated

[Mark Stephens]

I usually refer to Chip pearson 's page when I want to use arrays here's his instructions which seem very straightforward:

Dim Arr() As Variant ' declare an unallocated array.
Arr = Range("A1:C5")' Arr is now an allocated array


Here is my code which appears to follow his insturctions but when I check it the array is empty:


Dim MyArray() As Variant

With Workbooks(psRefFileName).Sheets(1)

MyArray = Range(Cells(1, 1), Cells(1000, 4))

End With


Can anyone spot what I am doing wrong it seems fine to me, why on earth will the array not collect the range assignment?

Any help very much appreciated

Kind regards, Mark

 

[Claus Busch]

Hi Mark,

Am Wed, 29 Jan 2014 06:34:52 -0800 (PST) schrieb Mark Stephens:

Dim MyArray() As Variant

try:
Dim myArray As Variant
without the brackets


Regards
Claus B.

 

[Mark Stephens]

I usually refer to Chip pearson 's page when I want to use arrays here's his instructions which seem very straightforward:



Dim Arr() As Variant ' declare an unallocated array.

Arr = Range("A1:C5")' Arr is now an allocated array





Here is my code which appears to follow his insturctions but when I checkit the array is empty:





Dim MyArray() As Variant



With Workbooks(psRefFileName).Sheets(1)



MyArray = Range(Cells(1, 1), Cells(1000, 4))



End With





Can anyone spot what I am doing wrong it seems fine to me, why on earth will the array not collect the range assignment?



Any help very much appreciated



Kind regards, Mark

Thanks for that Claus, it works but it is not collecting the right range from the workbook specified, I tried using

With workbook("MyWorkbook").Sheets(1)

End With
surrounding it but it appears to ignore that and use the main workbook rather than the active workbook (by virtue of just being opened). I also tried qualifying it fully which it also ignored! I am thinking of assigning the range to a named range then trying to assign it to my array / variant, do you know the syntax for that (I am somewhat rusty!).

Kind regards (and thanks), Mark

 

[Claus Busch]

Hi Mark,

Am Wed, 29 Jan 2014 06:57:51 -0800 (PST) schrieb Mark Stephens:

With workbook("MyWorkbook").Sheets(1)

End With

into a with statemant you have to put a dot in front of the ranges to
refer them to the right sheet:

Dim MyArray As Variant

With Workbooks(psRefFileName).Sheets(1)
MyArray = .Range(.Cells(1, 1), .Cells(1000, 4))
End With


Regards
Claus B.

 

[Mark Stephens]

I usually refer to Chip pearson 's page when I want to use arrays here's his instructions which seem very straightforward:



Dim Arr() As Variant ' declare an unallocated array.

Arr = Range("A1:C5")' Arr is now an allocated array





Here is my code which appears to follow his insturctions but when I check it the array is empty:





Dim MyArray() As Variant



With Workbooks(psRefFileName).Sheets(1)



MyArray = Range(Cells(1, 1), Cells(1000, 4))



End With





Can anyone spot what I am doing wrong it seems fine to me, why on earth will the array not collect the range assignment?



Any help very much appreciated



Kind regards, Mark

Hi Claus,

Thank you very much indeed, it worked!

Kind regards, mark