Selecting x cells to the left of the one you are on

[-DRB-]

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.

 

[Tom Ogilvy]

ActiveCell.FormulaR1C1 = "=INT(AVERAGE(RC[" & Temp & "]:RC[-1]))"