Thread: Using COUNT value as variable

I have a few named ranges on various worksheets. I can use VBA to hop back
and forth between these sheets with no difficulty. Now, I need my program to
count the cells in a named range and then store that value as a variable.

Example:
I have a named range called "lists" in a single column on a worksheet. I
need to engage a For loop that uses the number of cells in "lists" as its
upper bound. i.e. say "lists" contains 92 cells I need a line of code that
counts the number of cells in "lists" (92) and then sets x = 92. The size of
"lists" is not static, thus i can't just use the number 92; I actually need
the value returned by the count function to be stored as x for later use in a
for loop.

Any help would be greatly appreciated.

Thank you very much,
Nate

x = Range("Lists").Rows.count

if this code is in a sheet module and Lists is on another sheet or Lists is
a sheet level name (than actually Sheet1!Lists as an example), it will need
to be qualified with the sheet name where it exits

x = Worksheets("sheet1").Range("Lists").Rows.count

Actually hopping back and forth is generally counter productive and almost
never necessary.



--
Regards,
Tom Ogilvy



"Naterator" wrote:

> I have a few named ranges on various worksheets. I can use VBA to hop back
> and forth between these sheets with no difficulty. Now, I need my program to
> count the cells in a named range and then store that value as a variable.
>
> Example:
> I have a named range called "lists" in a single column on a worksheet. I
> need to engage a For loop that uses the number of cells in "lists" as its
> upper bound. i.e. say "lists" contains 92 cells I need a line of code that
> counts the number of cells in "lists" (92) and then sets x = 92. The size of
> "lists" is not static, thus i can't just use the number 92; I actually need
> the value returned by the count function to be stored as x for later use in a
> for loop.
>
> Any help would be greatly appreciated.
>
> Thank you very much,
> Nate

Tom,

Thanks for giving me a hand. When i used that code I immediately got a
runtime error '424' : Object required. What's going on with that?

Thanks again,
Nate

"Tom Ogilvy" wrote:

> x = Range("Lists").Rows.count
>
> if this code is in a sheet module and Lists is on another sheet or Lists is
> a sheet level name (than actually Sheet1!Lists as an example), it will need
> to be qualified with the sheet name where it exits
>
> x = Worksheets("sheet1").Range("Lists").Rows.count
>
> Actually hopping back and forth is generally counter productive and almost
> never necessary.
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Naterator" wrote:
>
> > I have a few named ranges on various worksheets. I can use VBA to hop back
> > and forth between these sheets with no difficulty. Now, I need my program to
> > count the cells in a named range and then store that value as a variable.
> >
> > Example:
> > I have a named range called "lists" in a single column on a worksheet. I
> > need to engage a For loop that uses the number of cells in "lists" as its
> > upper bound. i.e. say "lists" contains 92 cells I need a line of code that
> > counts the number of cells in "lists" (92) and then sets x = 92. The size of
> > "lists" is not static, thus i can't just use the number 92; I actually need
> > the value returned by the count function to be stored as x for later use in a
> > for loop.
> >
> > Any help would be greatly appreciated.
> >
> > Thank you very much,
> > Nate

Did you dimension x?


Sub Foo()
Dim x as Range

Set x = Range("RangeNameHere")

With x
'Do stuff
end With

Set x = Nothing
End Sub

Yes. I've declared x as an integer. I'm looking to use x as a variable, not
a range. I need x to be a number. I need to count the cells in the range
and that number needs to be x. Sorry i wasn't more specific

"Johnny" wrote:

> Did you dimension x?
>
>
> Sub Foo()
> Dim x as Range
>
> Set x = Range("RangeNameHere")
>
> With x
> 'Do stuff
> end With
>
> Set x = Nothing
> End Sub
>
>

> I have a named range called "lists" in a single column on a worksheet.

Here is a demo from the immediate window:

Worksheets("Sheet1").Range("B10:B200").name = "Lists"
? activeworkbook.names("Lists").RefersTo
=Sheet1!$B$10:$B$200
x = Range("Lists").Rows.Count
? x
191

Works fine for me. Perhaps you haven't communicated your situation
correctly.

--
Regards,
Tom Ogilvy

"Naterator" <Naterator@discussions.microsoft.com> wrote in message
news:441378FE-7D2C-4E28-A2C1-C4F3731ADDB2@microsoft.com...
> Tom,
>
> Thanks for giving me a hand. When i used that code I immediately got a
> runtime error '424' : Object required. What's going on with that?
>
> Thanks again,
> Nate
>
> "Tom Ogilvy" wrote:
>
> > x = Range("Lists").Rows.count
> >
> > if this code is in a sheet module and Lists is on another sheet or Lists
is
> > a sheet level name (than actually Sheet1!Lists as an example), it will
need
> > to be qualified with the sheet name where it exits
> >
> > x = Worksheets("sheet1").Range("Lists").Rows.count
> >
> > Actually hopping back and forth is generally counter productive and
almost
> > never necessary.
> >
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Naterator" wrote:
> >
> > > I have a few named ranges on various worksheets. I can use VBA to hop
back
> > > and forth between these sheets with no difficulty. Now, I need my
program to
> > > count the cells in a named range and then store that value as a
variable.
> > >
> > > Example:
> > > I have a named range called "lists" in a single column on a worksheet.
I
> > > need to engage a For loop that uses the number of cells in "lists" as
its
> > > upper bound. i.e. say "lists" contains 92 cells I need a line of
code that
> > > counts the number of cells in "lists" (92) and then sets x = 92. The
size of
> > > "lists" is not static, thus i can't just use the number 92; I actually
need
> > > the value returned by the count function to be stored as x for later
use in a
> > > for loop.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Thank you very much,
> > > Nate