Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set variable = to value of unknown cell
Good morning,
I have 2 questions: 1.) I need to copy several formulas from different columns in row 2 to a variable number of rows. For the first one, I use: a = Range("A65536").End(xlUp).Row Set SourceRange = Worksheets("Team").Range("e2") Set fillRange = Worksheets("Team").Range("e2:e" & a) SourceRange.AutoFill Destination:=fillRange The question is, if I want reuse this same code multiple times, do I just use "Set a = Nothing" after each use? Is "a" dimmed as a Range? 2.) The other, more pressing question, is: How do I set a variable equal to the result of a formula used on an unknown number of rows? For example, if the code above is a countif formula, how do I then say "sum this unknown range of cells then set mySum equal to it"? So somewhere else in my workbook I can code "There are " & mySum & " records meeting this criteria". I hope that's clear. Thanks in advance! Patti |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set variable = to value of unknown cell
"a" is storing a long. How you have it dimmed is unclear, but it isn't a
range and you would not do set a = nothing. As long as a is in scope (which would be dependent on where it is declared - but if not currently declared that would be in the single procedure where you use it) it will retain it value and it will change its value when you assign a different value to it. There is no reason to clear it first. If you wanted to "clear" a long, you would assign 0 to it - a = 0. a = Range("A65536").End(xlUp).Row Set SourceRange = Worksheets("Team").Range("e2") Set fillRange = Worksheets("Team").Range("e2:e" & a) SourceRange.AutoFill Destination:=fillRange mysum = application.Countif(fillrange,"ABC") mysum would hold a count of the cells in fillrange that display "ABC" You need to look in VBA help for an explanation of Scope of Variables. -- Regards, Tom Ogilvy "Patti" wrote in message ... Good morning, I have 2 questions: 1.) I need to copy several formulas from different columns in row 2 to a variable number of rows. For the first one, I use: a = Range("A65536").End(xlUp).Row Set SourceRange = Worksheets("Team").Range("e2") Set fillRange = Worksheets("Team").Range("e2:e" & a) SourceRange.AutoFill Destination:=fillRange The question is, if I want reuse this same code multiple times, do I just use "Set a = Nothing" after each use? Is "a" dimmed as a Range? 2.) The other, more pressing question, is: How do I set a variable equal to the result of a formula used on an unknown number of rows? For example, if the code above is a countif formula, how do I then say "sum this unknown range of cells then set mySum equal to it"? So somewhere else in my workbook I can code "There are " & mySum & " records meeting this criteria". I hope that's clear. Thanks in advance! Patti |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set variable = to value of unknown cell
Thanks for the explanation Tom!
"Tom Ogilvy" wrote in message ... "a" is storing a long. How you have it dimmed is unclear, but it isn't a range and you would not do set a = nothing. As long as a is in scope (which would be dependent on where it is declared - but if not currently declared that would be in the single procedure where you use it) it will retain it value and it will change its value when you assign a different value to it. There is no reason to clear it first. If you wanted to "clear" a long, you would assign 0 to it - a = 0. a = Range("A65536").End(xlUp).Row Set SourceRange = Worksheets("Team").Range("e2") Set fillRange = Worksheets("Team").Range("e2:e" & a) SourceRange.AutoFill Destination:=fillRange mysum = application.Countif(fillrange,"ABC") mysum would hold a count of the cells in fillrange that display "ABC" You need to look in VBA help for an explanation of Scope of Variables. -- Regards, Tom Ogilvy "Patti" wrote in message ... Good morning, I have 2 questions: 1.) I need to copy several formulas from different columns in row 2 to a variable number of rows. For the first one, I use: a = Range("A65536").End(xlUp).Row Set SourceRange = Worksheets("Team").Range("e2") Set fillRange = Worksheets("Team").Range("e2:e" & a) SourceRange.AutoFill Destination:=fillRange The question is, if I want reuse this same code multiple times, do I just use "Set a = Nothing" after each use? Is "a" dimmed as a Range? 2.) The other, more pressing question, is: How do I set a variable equal to the result of a formula used on an unknown number of rows? For example, if the code above is a countif formula, how do I then say "sum this unknown range of cells then set mySum equal to it"? So somewhere else in my workbook I can code "There are " & mySum & " records meeting this criteria". I hope that's clear. Thanks in advance! Patti |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
some unknown Cell styles are shown in cell styles | Excel Worksheet Functions | |||
Two unknown variable equation | Excel Worksheet Functions | |||
Find & replace a value in an unknown cell location | Excel Discussion (Misc queries) | |||
unknown object in cell | Excel Discussion (Misc queries) | |||
IF Function - If the reference cell data is presently unknown | Excel Worksheet Functions |