Set variable = to value of unknown cell

  • Thread starter Thread starter Patti
  • Start date Start date
P

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
 
"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.
 
Thanks for the explanation Tom!


Tom Ogilvy said:
"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
Click to expand...
 
Back
Top