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

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

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

On Wednesday, 29 January 2014 22:34:52 UTC+8, Mark Stephens wrote:
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

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

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

On Wednesday, 29 January 2014 22:34:52 UTC+8, Mark Stephens wrote:
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:)