Assigning named range to a 2-dimensional array

[Guest]

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.

 

[Guest]

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.

 

[Dave Peterson]

Dim AdjHoursArray As Range
Set AdjHoursArray = Range("AdjHoursLookup")
msgbox application.counta(adjhoursarray) & vblf & adjhoursarray.cells.count

=counta() will count all the formulas and constants that are in that
range--including formulas that evaluate to "" (like =if(a1=5,"ok",""))

 

[Guest]

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)

 

[Guest]

Dave,
Thanks for your help!
Bob

Dim AdjHoursArray As Range
Set AdjHoursArray = Range("AdjHoursLookup")
msgbox application.counta(adjhoursarray) & vblf & adjhoursarray.cells.count

=counta() will count all the formulas and constants that are in that
range--including formulas that evaluate to "" (like =if(a1=5,"ok",""))

 

[Guest]

Thanks!

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)