View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Toppers Toppers is offline
external usenet poster
 
Posts: 4,339
Default Calculating Non-Constant Values in Cells

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