Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving the cell selection in VB
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)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving the cell selection in VB
Try ActiveCell.Offset(1,1).Activate
"GSP@DCS" wrote: 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)" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving the cell selection in VB
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)" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving the cell selection in VB
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a selection... | Excel Discussion (Misc queries) | |||
Moving the Cell Selection Within AutoFilter | Excel Programming | |||
using a selection box and moving the accepted data | Excel Worksheet Functions | |||
Moving a Selection of Data??? | Excel Programming | |||
Moving a Selection of data??? | Excel Worksheet Functions |