Right... I think I answered my own question. I am using one loop per month,
with a counter and a placeholder that will record the last address. Since I
know where I start, this gives me the range to average. I then write a
worksheet function with the range to the workshet so that the user (and
myself) can alwys see where the data comes from. The code looks something
like this:

Dim r1 As Range 'first placeholder range
Dim MultiRange As Range 'final multirange
Dim counter As Integer 'counter
Dim Address1 As String 'Address placeholder
Dim Address2 As String 'Address placeholder
Dim AddressTotal As String 'Address placeholder

counter = 0
Address1 = "R7C2"

'count April
For Each r1 In ActiveSheet.Range("B6:IU6").Cells

If Not IsEmpty(r1.Value) Then

If r1.Value "1/4/2007" And r1.Value < "31/04/2007" Then

counter = counter + 1

Address2 = r1.Offset(1, 0).Address(ReferenceStyle:=xlR1C1)

End If

End If

Next

'creates the Address for April
AddressTotal = Address1 & ":" & Address2

'Pastes the function
Range("C20").Value = "=Average(" & AddressTotal & ")"


'Sets the start of May
Address1 = "R7C" & counter + 2

"Janos" wrote:

Hello,

I don't know if this is reallya difficult question, but its got me stumped.
What I am trying to achieve is to calculate a monthly average for individual
users. There is a table that looks like this:

User 23/04/2007 24/04/2007
Mike 41% 52%
Micheal 39% 45%
Jason 58% 55%


The first column contains the user in question, and the subsequent columns
the data for each day. The result is that all the data for one user is in one
row. The only (big) problem is that new columns of data will be added on a
daily, or quasi daily basis. This implies that i don't know how many columns
would be added for one particular month (so, no predifined spacings for the
calculations). The averages per user need to be calculated and formatted in
the following format:

User April May
Mike 40% 45%
Micheal 30% 50%
Jason 50% 40%

I couldn't for the life of me think of a way to do this with a worksheet
function, and other than a hunch of needing nested loops or doing one loop
per month per user, I'm stumped.

Any help would be greatly appreciated!

Thanks,

Janos