Using variables in SUBTOTAL functions in Excel/VBA

[Mark]

I am attempting to set up a program in Excel/VBA that
will take raw data that will format it, find the last
row, and Subtotal the column. Everything works except
the SUBTOTAL function.

I have a routine that starts at the top row and works
downward checking for the first empty cell. As it moves
down it tracks the Row by the varialbe "LineNumber" in a
loop with the line "LineNumber=LineNuber+1". When it
reachs the first empty cell I want to use
the "LineNumber" variable as the first arguement. The
Column will be constant in this application. I will then
Copy and Paste to other columns.

The code from recording a Macro is:
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-2235]C:R[-1]C)"

I have attemptd inserting the variable "LineNumber" with:
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-LineNumber]C:R[-
1]C)"

This does not work.

The SUBTOTAL range will always have a top row of 6 as
this is just below the column label. I am also using
AUTOFILTER's in the process to manipulate, then copy and
paste information.

I have attempted to "automate" SUBTOTAL's for a long time
and have yet to find the correct code. Please Help!

Thank you in advance,

Mark

 

[mark]

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-LineNumber]C:R[-

1]C)"

This does not work.

the reason that that doesn't work is that it hardcodes the
string 'LineNumber' into your formula, whereas the
term 'LineNumber' only means anything to your program, in
the context of your subroutine.

Use it like this:

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & LineNumber
& "]C:R[-1]C)"



In that, you are entering literal strings for the parts of
the formula that are not variables, but entering the
variable into the cell formula, not the name of the
variable.

Your variable likely actually isn't a string, but I just
tried it and Excel did the conversion on it's own as shown
above.