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.