How to use variables in ranges?

  • Thread starter Thread starter Darkeyce
  • Start date Start date
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
 
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...
 
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...
 
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...
 
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
 
Back
Top