Writing a macro to calculate average of cells

[Sedigh]

Hi Everybody,
I need to write a macro on my Excel sheet to calculate the average of cells for me. This is the code I have written but the average function is not working.
Can you please let me know how can I use the Average function in my code.
Thanks alot

Sub Macro1()
Ncol = 1
nrowColumn2 = 1
i = 1
Do Until IsEmpty(Worksheets(1).Cells(i, 1))
  j = i + 7
  Worksheets(1).Cells(i, 2).Select
 
  ActiveCell.Value2 = "=
  AVERAGE(Worksheets(1).Cells(i,1).value2:Worksheets(1).Cells(j,1).value2)"
 
  Modi = i Mod 3
  If Modi = 0 Then
     i = i + 10
  Else
     i = i + 1
  End If
  nrowColumn2 = nrowColumn2 + 1
Loop
End Sub

[Killer42]

You're including the variable names i & j in the formula. Is that what you intended? I wouldn't think that Excel would accept them when calculating.

In other words, if you simply typed this formula into a cell in an Excel spreadsheet, would you expect it to work? (I'm not saying it won't, as I don't know for sure)

=AVERAGE(Worksheets(1).Cells(i,1).value2:Worksheet s(1).Cells(j,1).value2)

Ok, I just tried pasting it in, and Excel obviously doesn't like it. I would suggest you're getting your variable names and their values confused. You probably need to build that string quite differently. Could you explain in more detail what is supposed to go into the formula? For instance, does "Worksheets(1).Cells(i,1).value2" refer to the start of the range you want to average? Or does it refer to a cell whose value identifies the start of the range? (I mean, perhaps this cell contains "$A$7" or something, and you want to use that as a cell reference). And why value2 rather than value?