View Full Version : AVERAGE PROBLEM
oleg_v
03-03-2010, 12:03 AM
Hello
I need help with a macro:
i have a column of data in sheet1
column Q
-0.001 -0.001 0.002 0.000 0.000 -0.006 -0.001 -0.001 0.003 0.001 -0.004 -0.003 -0.001 -0.001 0.005 -0.004 0.004 -0.006 0.000 0.005 -0.008 -0.006 0.000 -0.006
i need that macro will calculate the average of each 4 consecutive parts starting with "Q2"
and the average results will paste in column "B" starting with "B2"
thanks
Try:
Sub exa()
Dim i As Long
i = 2
Do While Not i >= Cells(Rows.Count, "Q").End(xlUp).Row + 1
With Cells(Cells(Rows.Count, "B").End(xlUp).Offset(1).Row, "B")
.Formula = "=AVERAGE(" & Range(Cells(i, "Q"), Cells(i + 3, "Q")).Address & ")"
.Value = .Value
End With
i = i + 4
Loop
End Sub
Hope that helps,
Mark
oleg_v
03-03-2010, 01:38 AM
Thanks it works
only 1 question how can i activate this macro from other sheet?
Thanks it works
only 1 question how can i activate this macro from other sheet?
I am not quite sure what you are asking refrence "...from the other sheet."
The code should really go in a Standard Module, not a sheet's module.
If you are asking how to make sure the code acts against a certain sheet, regardless of what sheet is active, the answer is to set a reference to the sheet and qualify range references. Something like:
Sub exa()
Dim i As Long
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Sheet1")
i = 2
Do While Not i >= wks.Cells(Rows.Count, "Q").End(xlUp).Row + 1
With wks.Cells(wks.Cells(Rows.Count, "B").End(xlUp).Offset(1).Row, "B")
.Formula = "=AVERAGE(" & Range(wks.Cells(i, "Q"), wks.Cells(i + 3, "Q")).Address & ")"
.Value = .Value
End With
i = i + 4
Loop
End Sub
Hope that helps,
Mark
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.