cuewoz,

Hmmm..... Not sure _exactly_ what you mean, but let's say that the user
gives you the number of rows and columns (in the code below, the RowCount =
10 and ColCount = 2). For a range starting two rows below the activecell,
you could use something like this, that will average a 10 row by 2 column
range that starts at B5: It will result in the formula

=AVERAGE($B$5:$C$14)

in cell B3.

Sub Test()

Dim RowCount As Long
Dim ColCount As Integer

RowCount = 10
ColCount = 2

Range("B3").Select

ActiveCell.Formula = "=AVERAGE(" & Range(ActiveCell.Offset(2, 0), _
ActiveCell.Offset(RowCount + 1, ColCount - 1)).Address & ")"

End Sub

Take out the Range("B3").Select and get the rowcount and colcount from
inputboxes.....

HTH,
Bernie
MS Excel MVP



"cuewoz" wrote in
message ...

Hi Bernie
Thanks for that, but I cant get it working.
If it makes a difference I am running excel 2002.

The formula:
Activecell.Formula = "=AVERAGE(" & Range(Cells(3, 4), Cells(11,
5)).Address
& ")"
Is like the one I am after and so I can insert some letter variables in
the cells positions.

You see the active cell where I want the average to be is offset from
the list of numbers, and I get the amount of numbers in the list from
the user, then when the user clicks a command button, it generates the
list of numbers. it does this ok and now I need to reference to them
but I can't use the range("AB17:AB19") format for example because I
need to have the letters and numbers in describing the range variables
so I can easily change the area of range depending on the length of
list from the user, and because the length can be anything, I need to
be able to do this.

This is very frustrating as on the face of it appears like it should be
straightforward, but I can't see the solution.

Thanks


--
cuewoz
------------------------------------------------------------------------
cuewoz's Profile:
http://www.excelforum.com/member.php...o&userid=32161
View this thread: http://www.excelforum.com/showthread...hreadid=570900