Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array assignment | Excel Programming | |||
Work sheet hangs - help appreciated | Excel Programming | |||
Work sheet hangs - help appreciated | Excel Programming | |||
Formulas assignment from array to range in VSTO Excel doesn't work | Excel Worksheet Functions |