By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

Writing a macro to calculate average of cells

P: 1
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

Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2. Ncol = 1
  3. nrowColumn2 = 1
  4. i = 1
  5. Do Until IsEmpty(Worksheets(1).Cells(i, 1))
  6.   j = i + 7
  7.   Worksheets(1).Cells(i, 2).Select
  8.  
  9.   ActiveCell.Value2 = "=
  10.   AVERAGE(Worksheets(1).Cells(i,1).value2:Worksheets(1).Cells(j,1).value2)"
  11.  
  12.   Modi = i Mod 3
  13.   If Modi = 0 Then
  14.      i = i + 10
  15.   Else
  16.      i = i + 1
  17.   End If
  18.   nrowColumn2 = nrowColumn2 + 1
  19. Loop
  20. End Sub
Aug 10 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 5K+
P: 8,434
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?
Aug 10 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.