Sum Cells in range above in Excel 2000 VBA

[grego9]

I am trying to write some VBA in Excel 2000 that scans down column L and inserts the Autosum command in each blank cell.

Outside of VBA when I click the autosum command excel sums all the cells above it up to the blank cell above. That is to say that it automatically works out what the range is that I want to sum (and this is fine!)

When I record this as a macro - and go to view the code it specifies the cells that the autosum calculated needed summing - rather than just registering that I had clicked on Autosum

NB I will be applying this macro to data whose ranges will change so this is no use to me!

I simply want the macro to include the code for clicking on Autosum

This is what I have so far:

Columns("L:L").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=SUM(R[-177]C:R[-1]C)"

Is there any way to get the formula to simply equal pressing autosum?
When I put in "=Sum()" it doesn't like it as it seems to need me to specify the range!

HELP!

[grego9]

I've sussed it! - I found the following worked perfectly.

Sub AutoSum()
CommandBars.FindControl(ID:=226).Execute
Application.SendKeys "~"
End Sub

There are a list of other control ID's on the microsoft website

[Killer42]

... There are a list of other control ID's on the microsoft website

You're too quick for us! :)

Thanks for the info about the control IDs, it sounds potentially quite useful.

[grego9]

You're too quick for us! :)

Thanks for the info about the control IDs, it sounds potentially quite useful.

The Microsoft Excel 2000 control ID list can be found at http://support.microsoft.com/kb/213552.

I think the numbers might be different for different versions of excel

thanks

[ARWien]

Hey Greg,

Thanks for the information. I was having the same problem as well and found your post online.

However, I am having a problem with the code. After I run the following code:

Sub Autosum()

CommandBars.FindControl(ID:=226).Execute
Application.SendKeys "~"

End Sub

I cannot get the autosum to stay in the active cell. The code never closes out of the cell but leaves me with the blinking cursor and the ability to edit that cell w/o clicking on it. I have to manually push enter after the macro to keep the autosum and exit out of the active cell.

Also, if at the end of the code, I select another cell (i.e. Range("A1").Select), the autosum does not stay in the cell that I want it in but goes to the cell I select. It is a vicious cycle that I cannot get out of.

Any suggestions?

Thanks.