Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert formulas in the last row of different worksheets | Excel Programming | |||
Macro that will Insert Formulas in last row of different worksheet | Excel Programming | |||
How to insert formulas from four worksheets into one worksheet | Excel Worksheet Functions | |||
Macro to insert copy and insert formulas only to next blank row | Excel Programming | |||
insert scanned worksheets,then apply formulas, similiar to backgr. | Excel Programming |