Excel VBA - Put totals at the bottom of specific column 1

[ejsmith]

I'm trying to write a sub to place a subtotal (or count) at the bottom of a specified column given the spreadsheet, the first cell in the range and the type of summary operation to perform.
This is easy if you hardcode the first cell in the range - but I can't get it to work when the top cell is passed in as a parameter!
Any help/suggestions/code would be greatly appreciated!
Thanks!
Here is what I have so far...

Public Sub GetTotals(OutputFileName, TopCellInRange, SummaryFunction)
'OutputfileName is the Excel spreadsheet to operate on
'TopCellInRange is the first (top) cell in the range we wish to operate on
'SummaryFunction is either "Sum" or "Count"... to get the appropraite excel formula
' e.g., =Sum(RC:RC) or =Count(RC:RC)

Dim TopCellFmt
Dim X As Object
Dim Outputfile As String
Set X = CreateObject("Excel.application&quot
X.workbooks.Open (OutputFileName)

X.Range(TopCellInRange).Select
TopCellFmt = X.ActiveCell.Address()
X.Range(TopCellInRange).End(XlDirection.xlDown).Select
X.ActiveCell.Offset(Rowoffset:=1, ColumnOffset:=0).Select
X.ActiveCell.FormulaR1C1 = "=Sum(TopCellFmt:R[-1]C)"
X.ActiveCell.Select


'Save and Close Excel Report OutputFile
X.Application.DisplayAlerts = False
X.ActiveWorkbook.SaveAs OutputFileName
X.Application.DisplayAlerts = True

' Close Excel with the Quit method on the Application object.
X.Application.Quit
' Release the object variable.
Set X = Nothing

End Sub

 

[SkipVought]

Hi,
Why don't you pass the ColumnIn and RowIn of the TopCell.
Then the range to SUM/COUNT is...

Range(Cells(RowIn, ColumnIn), _
      Cells(RowIn, ColumnIn).End(xlDown))

The cell to contain the SUM/COUNT is ...

Cells(RowIn, ColumnIn).End(xlDown).Offset(1,0)

Skip

 

[ejsmith]

Thanks Skip. I'll try it!