Macro to insert formulas on last row of different worksheets
 

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

Macro to insert formulas on last row of different worksheets
 

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