Hi,
I am sure there is a more elegant solution but the following works!
Sub sumvar(rng)
Dim v As Variant
Dim sr As Long, fr As Long
rng.Select
sr = Selection.Row ' Start row
v = Split(rng.Address(columnAbsolute:=False), "$") ' Column letter
Selection.End(xlDown).Select ' Last row of data to be summed
fr = Selection.Row ' finish row
' SUM in next row .....
ActiveCell.Offset(1, 0).Formula = "=SUM(" & v(0) & sr & ":" & v(0) & fr & ")"
End Sub
Sub test()
Call sumvar(Range("AB1"))
End Sub
HTH
"sesmith" wrote:
After importing a file from Access to Excel, and formatting the
spreadsheet, I need to calculate a sum. The problem is that the cell
references do not remain the same. The amount of rows to add depend on
the information being imported.
In the Macro, I have accessed the correct cell for the calculation by
the "EndDown" feature. I then press the sum key on the toolbar, but it
keeps the same count of rows to sum, which I mentioned differs. Is
there a way to sum the rows with variance each time? Below is what I
have coded for one column.
(The cell "O11" is always the beginning cell for the calculation.)
Range("O11").Select
Selection.End(xlDown).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
I appreciate any help available!!
Shelley
--
sesmith
------------------------------------------------------------------------
sesmith's Profile: http://www.excelforum.com/member.php...o&userid=24765
View this thread: http://www.excelforum.com/showthread...hreadid=384563