loop to avergae out colums numbers.

[barrowinfurnace]

Hi all
another tricky one here
lets say I have ten sheets in my workbook and in column E of these sheets I have a series of numbers.The number of cells occupied will vary from sheet to sheet.
I am looking to find the average of each column of numbers,then place this average in sheet 11 in the relevent cell.ie average from sheet 1 will go into A1 in sheet 11,average from sheet 2 will go into A2 in sheet 11 and so on.
The problem is a bit tricky because the number of cells occupied in the colE of each sheet will vary
This can be done manually with the average function in autosum(creating a macro) but the problem here is that because the number of cells on each sheet varies and the"average" figure gets placed down in the last cell of the sheet(1048576!)
Is there a way this could be done in vb.either with or without a loop.
1)open sheet 1
2)read to the last cell
3)average out the cells in the column
4)place the figure in sheet 11
5)then go onto next sheet an so on
any help much appreciated.
Kind Regards
bif

[Kaper]

Hi, as result is to be placed in separate sheet you can use just

Please Login or Register  to view this content.

in A1 Sheet11 and similar formulas in A2 etc.
of course it can be done with a loop in VBA something like (not tested):

Please Login or Register  to view this content.

[barrowinfurnace]

Hi Kaper,
Thankyou for the code and advice(and quick response).The code works brilliantly,and the formula will also come in very useful.

many thanks
kind regards
BIF

[Kaper]

Glad it worked, and thanks for nice words :-)