Autosum

[Guest]

I have written a macro in excel, which needs to transfer a figure from one
part of the spreadsheet to another. However, the required figure is the total
of a variable number of data entries. So, before I can run the macro, I have
to click on the autosum button on the toolbar. I want to have this as part of
the macro, but whenever I try and record it in a macro, if there are, say 5
figures to be totalled and then transferred, the macro records the 5 cells.
Howvere, that is no good to me if the next figure is the total of, say, 7 or
2 cells - the macro calculates from 5 cells. How can I get the macro to work
like it does when you hit the autosum button - it recognises how many cells
are in the column to be added together, whether it be 2, 5, 7 etc?

 

[Dave Peterson]

Maybe you can just do the equivalent in code.

This sample looks at the cell above and then does End|UpArrow to find out what
should be summed.

Option Explicit
Sub testme()
Dim RngToAutoSum As Range
Dim myCell As Range
Dim TopCell As Range

Set myCell = ActiveCell

With myCell
If IsEmpty(.Offset(-1, 0).Value) Then
Beep 'nothing right above
Else
If IsEmpty(.Offset(-2, 0).Value) Then
Set TopCell = .Offset(-1, 0)
Else
Set TopCell = .Offset(-1, 0).End(xlUp)
End If

Set RngToAutoSum = .Parent.Range(.Offset(-1, 0), TopCell)
myCell.Formula = "=sum(" & RngToAutoSum.Address(0, 0) & ")"
End If
End With

End Sub


If you're not using =sum(), you can modify the code. If you're not using the
cells above, you can modify the code to use rows.

And you can add as many checks as you want.

 

[Guest]

Thank you very much for your help - I will try this out and see how I get on
with it...