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.

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.