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.

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.
>

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",""))



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.
> >

--

Dave Peterson

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.
> >

Dave,
Thanks for your help!
Bob


"Dave Peterson" wrote:

> 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",""))
>
>
>
> 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.
> > >
>
> --
>
> Dave Peterson
>

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.
> > >