Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Selecting x cells to the left of the one you are on
Hi,
I've been working on some macros that help me average and order some pupils' marks in tests we have at school. I've been using: ActiveCell.FormulaR1C1 = "=INT(AVERAGE(RC[-3]:RC[-1]))" Selection.Copy 'Move down a cell, select rest of pupils and paste ActiveCell.Offset(1, 0).Select Range(ActiveCell, ActiveCell.Offset(Pupils - 2, 0)).Select ActiveSheet.Paste This works fine, with the first line of code selecting the cell to the left of the one I'm putting the average in [RC-1], then another two cells (i.e. to RC[-3]). Sorry if I'm pointing out the obvious here to the experts. But I want the number of cells to the left to be calculated by Excel, i.e. the RC[-3]. Now, Excel knows the number of tests the pupils have done and I can easily set a variable to be the right value. But, if I set this up with the value going into Temp and have: ActiveCell.FormulaR1C1 = "=INT(AVERAGE(RC[Temp]:RC[-1]))" Then Excel spits this out. Any ideas with my problem folks? Many thanks, Dave. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Selecting x cells to the left of the one you are on
ActiveCell.FormulaR1C1 = "=INT(AVERAGE(RC[" & Temp & "]:RC[-1]))"
-- Regards, Tom Ogilvy "-DRB-" wrote in message ... Hi, I've been working on some macros that help me average and order some pupils' marks in tests we have at school. I've been using: ActiveCell.FormulaR1C1 = "=INT(AVERAGE(RC[-3]:RC[-1]))" Selection.Copy 'Move down a cell, select rest of pupils and paste ActiveCell.Offset(1, 0).Select Range(ActiveCell, ActiveCell.Offset(Pupils - 2, 0)).Select ActiveSheet.Paste This works fine, with the first line of code selecting the cell to the left of the one I'm putting the average in [RC-1], then another two cells (i.e. to RC[-3]). Sorry if I'm pointing out the obvious here to the experts. But I want the number of cells to the left to be calculated by Excel, i.e. the RC[-3]. Now, Excel knows the number of tests the pupils have done and I can easily set a variable to be the right value. But, if I set this up with the value going into Temp and have: ActiveCell.FormulaR1C1 = "=INT(AVERAGE(RC[Temp]:RC[-1]))" Then Excel spits this out. Any ideas with my problem folks? Many thanks, Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do cells run left to right | Setting up and Configuration of Excel | |||
Selecting multiple cells, I can't see the cells highlighted | Excel Discussion (Misc queries) | |||
How to change shade of cells when selecting multiple cells | Excel Discussion (Misc queries) | |||
By selecting cells adjacent to cells tally sheet | Excel Worksheet Functions | |||
selecting cells | Excel Discussion (Misc queries) |