Both relative and absolute references in Subtotal

[Gus68]

Hi All,

Is it possible to use both relative and absolute cell references in a subtotal macro?

We're trying to create a subtotal within our macro that uses cell E3 as the absolute and the cell above the active cell as the relative reference. I just can't work out the syntax (if it's even possible!!)

I get a compile error when I use the following:-
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,"E3":R[-1]C)"

Can anybody point me in the right direction? And apologies if this has been posted previously - none of my searches returned a hit.

 

[shg]

You can't mix A1 and R1C1 references. Maybe

... = "=SUBTOTAL(9, R3C:R[-1]C)"

 

[syntaxed]

Or:

activecell.Formula = "=SUBTOTAL(9,E3:" & Left(Cells(1, activecell.Column).Address(1, 0), InStr(1, Cells(1, activecell.Column).Address(1, 0), "$") - 1) & activecell.Row-1 & ")"

 

[Gus68]

Thank you both for your replies!

I was 99% sure that you wouldn't be able to use both absolute and relative but thought I'd ask the question just in case.

Shg's suggestion does the job simply enough for what we need.. thanks!!