Calculating Non-Constant Values in Cells

[sesmith]

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

 

[Guest]

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]

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

 

[Guest]

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]

THT,

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

THANKS AGAIN!
Shelley :

 

[Guest]

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