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 |
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 |
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 |
All times are GMT +1. The time now is 08:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com