moving the cell selection in VB

[GSP@DCS]

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)"

 

[Don Guillett]

Sub getrng()
Range("a5").End(xlToRight).End(xlDown).Select
'or below to offset 1 to the left
'Range("a5").End(xlToRight).End(xlDown).Offset(, -1).Select
End Sub

however you shouldn't need to SELECT
myrng=Range("a5").End(xlToRight).End(xlDown)
try this idea
Range("a5").End(xlToRight).End(xlDown) _
..Offset(, -1).Formula = "=2*5"

 

[DoctorG]

Try ActiveCell.Offset(1,1).Activate

 

[GSP@DCS]

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

 

[Don Guillett]

close

Sub formulavariable()
of = ActiveCell.Formula
MsgBox of
ActiveCell.Offset(1) = of
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

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

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)"