Hi,
Alt+F11 to open
VB editor. Right click 'ThisWorkbook' and insert module and
paste this in
Sub standard()
Dim ws As Worksheet
For x = 1 To Worksheets.Count
lastrow = Sheets(x).Cells(Rows.Count, "AB").End(xlUp).Row
Sheets(x).Range("AB" & lastrow + 1).FormulaArray = _
"=AVERAGE(IF(N1:N" & lastrow & "0,IF(P1:P" & lastrow & "<110,AB1:AB" &
lastrow & ")))"
Sheets(x).Range("AB" & lastrow + 2).FormulaArray = _
"=AVERAGE(IF(N1:N" & lastrow & "=0,IF(P1:P" & lastrow & "<110,AB1:AB" &
lastrow & ")))"
Next
End Sub
Mike
"ScottMSP" wrote:
Hello,
I need a macro that will insert two formulas below the last row of column AB
in every worksheet I have in a workbook. The number of rows in each
worksheet varies so I never know what the last row is. In addition, I need
to include headers to the left of each cell that contains the formula.
The two formulas that I need to insert average data based on conditions in
two other columns. The two formulas a
Average Increase {=AVERAGE(IF(([RANGE IN COLUMN N]0)*([RANGE IN COLUMN
P]<110), [RANGE IN COLUMN AB]))}
Casual Average Increase {=AVERAGE(IF(([RANGE IN COLUMN N]=0)*([RANGE IN
COLUMN P]<110), [RANGE IN COLUMN AB]))}
Thanks in advance.
-Scott