Both replies have helped a lot so far. Now I need to be able to assign to a
var the formula that is in the cell when I arrive. Then I can test it and
modify if needed. I've tried;

old_formula = Cells(ActiveCell.Formula)

but this gives a data type mismatch. I've used the cells(x,y) function but
this only works if I know what x and y are. Is thre a way to determine cell
reference for the active cell?

tia
steve


"GSP@DCS" wrote in message
.. .
I need to know which commands to use in VB to move the cursor as if
pressing the arrow keys in Excel.

I have a data set which contains sales information in 5 columns. They are
salesperson, days to deliver, qty sold, amnt of sale and commission. I
want to add a subtotal which sums the qty, amnt and commission but
averages the days. In Excel if I do a sub-total for the sum (replace) then
a sub-total for hte average (do not replace) then two separate rows are
inserted, one for each function. If I do one sub-total and sum all four
metrics, then go back and change the function ref from 9 to 1 for the
average evrything is as desired.

The VB module looks like this:

This section performs the sub-total to sum all four metrics.

' select cell in upper left corner of sub-total data
Range("A5").Select
' = ctrl/shift-right arrow
Range(Selection, Selection.End(xlToRight)).Select
' = ctrl/shift-down arrow
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
' for every change in salesperson sum metrics
Selection.Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=Array(2, 3, 4, 5), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True


Then I want to navigate to the days sub-total and change it:


Select the column just to the left of the one I want to modify because the
cursor will stop at the balnk cell in the sub-total row.
Range("A5").Select -

' = ctrl-down arrow
Selection.End(xlDown).Select

Here is where I need to arrow down 1 then right 1 to be on the cell I
want to modify.
I tried SendKeys ("{DOWN}") but this does not work

When I get to the cell I will modify with
ActiveCell.FormulaR1C1 = "=SUBTOTAL(1,R[-27]C:R[-1]C)"