About Us

Bob

Thanks!

"p45cal" wrote:

Note that te title of this thread seems at odds with the code quoted;
Dim AdjHoursArray As Range
declares a Range object, which is not an array.

To assign the named range to an array do this:

Dim AdjHoursArray As Variant 'optional, but required if Option Explicit on.
AdjHoursArray = Range("AdjHoursLookup")

AdjHoursArray is now an array of variants (1 to 3, 1 to 4)

--
p45cal

"Bob" wrote:

I just discovered that by removing ".Value", it fixes the "object required"
error message problem.

But now I have another issue. Since the size of the named range will vary
over time, is there a way I can determine the number of non-blank elements in
the array?

Thanks again for any help.

"Bob" wrote:

I have a named range ("AdjHoursLookup") that currently occupies 12 cells (3
rows x 4 columns), but the number of cells will vary over time.

Is it possible to assign the named range to a 2-dimensional array? When I
excute the following two lines of code, for example, I get an "object
required" error message.

Dim AdjHoursArray As Range
Set AdjHoursArray = Range("AdjHoursLookup").Value

Any help would be greatly appreciated. Thanks.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Insert into a range from a two dimensional array	[email protected][_2_]	Excel Programming	13	May 29th 07 06:29 AM
Load excel range into multi dimensional array	Rishi Dhupar	Excel Programming	1	January 26th 06 03:50 PM
Extracting unique entries and assigning it to a named range	Hari	Excel Discussion (Misc queries)	0	December 13th 05 06:29 AM
Extracting unique entries and assigning it to a named range	Hari	Excel Programming	3	December 12th 05 09:58 AM
Transferring part of a multi-dimensional array to a range in VBA	Bob J.[_3_]	Excel Programming	1	July 27th 05 03:38 PM

Menu

About Us