Macro to add subtotal forumla using named ranges

[Legacy 173085]

I'm working on a macro to automate some reports I run at work every week and am stuck at trying to get my macro to add a subtotal formula to the bottom of the last column.

What I've been trying to do is adapt a macro which finds the last cell (by ctrl jumping down to bottom and moving offset(7, 0)) - from here names the cell at Offset(-2, 0) as "BOTTOM", and cell I8 as "TOP" - then writes them into my subtotal formula (i.e. Activecell =subtotal(3,TOP:BOTTOM) --- but this doesn't seem to work....

I don't really know what i'm doing i'm very new to vba. thanks in advance for any help you can give!

 

[stilesbn]

Try using the following instead of activecell = subtotal(3,TOP:BOTTOM)

Activecell.FormulaR1C1 = "=SUBTOTAL(3,TOP:BOTTOM)"

 

[Legacy 173085]

Thanks Stilesbn

I've put that in and it brings up #NAME? error - I think maybe I'm not naming the ranges properly? Not really sure what I'm doing...

My code is:

Dim BOTTOM As Range
Dim TOP As Range

Set BOTTOM = ActiveCell
Set TOP = Range("I8")

ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,TOP:BOTTOM)"

Hope you can help

 

[stilesbn]

Oh, I thought you were defining names in the excel sheet not setting them as variables in the Macro. Try this...

ActiveCell.FormulaR1C1 = "=SUBTOTAL(3," & TOP & ":" & BOTTOM & ")"

I'm actually not sure this will work. But it might!

 

[stilesbn]

I tested it and for me it didn't work, however, modifying it so that Bottom is a set of what row and columns it is on and then taking out "TOP" and entering it directly into the formula in R1C1 format (I8 is R8C9, or Row 8 Column 9) it worked out.

Dim BR As Integer
Dim BC As Integer
Dim TOP As Range

BR = ActiveCell.Row
BC = ActiveCell.Column

ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R8C9:R" & BR & "C" & BC & ")"

 

[Legacy 173085]

You're a star this totally worked thank you so much!