Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using COUNT value as variable
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using COUNT value as variable
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using COUNT value as variable
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using COUNT value as variable
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using COUNT value as variable
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count number of dates in a row of cel before or after a variable d | Excel Worksheet Functions | |||
Count in a variable range | Excel Worksheet Functions | |||
Count if multiple variable exist | Excel Discussion (Misc queries) | |||
Multiple Variable Count problem | Excel Worksheet Functions | |||
variable row count 2 | Excel Programming |