Set variable = to value of unknown cell

[Patti]

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

 

[Tom Ogilvy]

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

 

[Patti]

Thanks for the explanation Tom!

"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



equal