How to use variables in ranges?

D

Darkeyce

can someone please explain to me how to use a variable in a rang
reference?

example:
maxpolines = Application.CountIf(Worksheets("P
Data").Range("A2:A500"), srcponum)

...some autofilter logic....

Worksheets("PO Data").Select
tottoinv = Evaluate("=SUBTOTAL(9,C2:C500)")
Click to expand...


The above yields the results i want. but i would like to replace "C500
with some reference to the maximum lines as identified b
'maxpolines'.

the macro recorder produced "=SUBTOTAL(9,R[-14]C:R[-1]C)". This synta
looks like some kind of offset reference but i have no idea how to mak
something useful (and flexible) out of that
 
F

Frank Kabel

Hi
you may try
tottoinv = Evaluate("=SUBTOTAL(9,C2:C" & maxpolines+1 & ")")


--
Regards
Frank Kabel
Frankfurt, Germany
can someone please explain to me how to use a variable in a range
reference?

example:
maxpolines = Application.CountIf(Worksheets("PO
Data").Range("A2:A500"), srcponum)

...some autofilter logic....

Worksheets("PO Data").Select
tottoinv = Evaluate("=SUBTOTAL(9,C2:C500)")
Click to expand...


The above yields the results i want. but i would like to replace
"C500" with some reference to the maximum lines as identified by
'maxpolines'.

the macro recorder produced "=SUBTOTAL(9,R[-14]C:R[-1]C)". This syntax
looks like some kind of offset reference but i have no idea how to
make something useful (and flexible) out of that.
Click to expand...
 
B

Bob Phillips

Hi Darkeyce,

maxpolines = Application.CountIf(Worksheets("PO Data").Range("A2:A500"),
srcponum)

' ...some autofilter logic....

Worksheets("PO Data").Select
tottoinv = Evaluate("=SUBTOTAL(9,C2:C" & maxoplines & ")")

The R1C1 reference that you see relates to the active cell {-14 is 14
columns/rows before) and is not necessary to get the subtotal you need.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Darkeyce > said:
can someone please explain to me how to use a variable in a range
reference?

example:
maxpolines = Application.CountIf(Worksheets("PO
Data").Range("A2:A500"), srcponum)

...some autofilter logic....

Worksheets("PO Data").Select
tottoinv = Evaluate("=SUBTOTAL(9,C2:C500)")
Click to expand...


The above yields the results i want. but i would like to replace "C500"
with some reference to the maximum lines as identified by
'maxpolines'.

the macro recorder produced "=SUBTOTAL(9,R[-14]C:R[-1]C)". This syntax
looks like some kind of offset reference but i have no idea how to make
something useful (and flexible) out of that.
Click to expand...
 
D

Dave Peterson

And maybe drop the evaluate:

tottoinv = application.SUBTOTAL(9,range("C2:C" & maxpolines)

maybe better would be to give it the worksheet, too:

tottoinv _
= application.SUBTOTAL(9,worksheets("po data").range("C2:C" & maxpolines))


Darkeyce < said:
can someone please explain to me how to use a variable in a range
reference?

example:
maxpolines = Application.CountIf(Worksheets("PO
Data").Range("A2:A500"), srcponum)

...some autofilter logic....

Worksheets("PO Data").Select
tottoinv = Evaluate("=SUBTOTAL(9,C2:C500)")
Click to expand...

The above yields the results i want. but i would like to replace "C500"
with some reference to the maximum lines as identified by
'maxpolines'.

the macro recorder produced "=SUBTOTAL(9,R[-14]C:R[-1]C)". This syntax
looks like some kind of offset reference but i have no idea how to make
something useful (and flexible) out of that.
Click to expand...
 
D

Darkeyce

wow!
you guys are good.

Looking at the solutions you provided, everything looks so clear...tha
thing was beating me to death.

Thanks for the help
 
Top