Calculating Non-Constant Values in Cells

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

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
>
>

HTH,

I tried that, but it didn't work for me. Is there part of the code I should be changing? It's throwing an error out at the Sum calculation and placing the active cell as the very last cell available for the entire spreadsheet. Help?!

Sincerely,
Shelley

Hi,
My testing ( for example) I had data in rows 11 to 17, and SUM was
in row 18. Another test I had data in rows 1 to 10, and SUM was in 11.
Repeated running of the macro calculated SUM in the next row down i.e. 11, 12
,13.

I assumed the data was contiguous and I cannot see why it goes to the end
cell. No manual intervention is required to select the SUM cell - the macro
does it for you..

If there are blanks in the data, the SUM will be in the first blank cell it
meets. You could change this by using:

fr = Cells(Rows.Count, v(0)).End(xlUp).Row
Cells(fr, v(0)).Select

Try this.

HTH

"sesmith" wrote:

>
> HTH,
>
> I tried that, but it didn't work for me. Is there part of the code I
> should be changing? It's throwing an error out at the Sum calculation
> and placing the active cell as the very last cell available for the
> entire spreadsheet. Help?!
>
> Sincerely,
> Shelley
>
>
> --
> sesmith
> ------------------------------------------------------------------------
> sesmith's Profile: http://www.excelforum.com/member.php...o&userid=24765
> View this thread: http://www.excelforum.com/showthread...hreadid=384563
>
>

THT,

You have been such a blessing! All I needed to do was change the range that was first selected. (I should've known that!) It works perfectly and my project is now completed.

THANKS AGAIN!
Shelley

Glad to have been of service and that is all fixed.

"sesmith" wrote:

>
> THT,
>
> You have been such a blessing! All I needed to do was change the range
> that was first selected. (I should've known that!) It works perfectly
> and my project is now completed.
>
> THANKS AGAIN!
> Shelley
>
>
> --
> sesmith
> ------------------------------------------------------------------------
> sesmith's Profile: http://www.excelforum.com/member.php...o&userid=24765
> View this thread: http://www.excelforum.com/showthread...hreadid=384563
>
>