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?


--
AW

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.


Alan wrote:

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?

--
AW

--

Dave Peterson

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


"Dave Peterson" wrote:

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.


Alan wrote:

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?

--
AW

--

Dave Peterson