Macro to insert formulas in the last row of different worksheets

[ScottMSP]

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 are:

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

 

[Paul]

Scott,

Try this for size:

It makes the following assumptions:
Every worksheet has the same kind of structure.
The data is in a nice block; in other words, no information in N and P lies
below the last entry in AB.
The information block begins in Row 1 (Header or not).
There are at least TWO clear rows at the bottom of column AB.

Sub Main()
'Purpose: to run InsertAVGFormulas procedure on every worksheet.
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Select
Call InsertAVGFormulas
Next
End Sub

Sub InsertAVGFormulas()
'Purpose: Inserts array formulas at the bottom of Col AB.
Dim lLastRow As Long
Dim sAdrsN As String
Dim sAdrsP As String
Dim sAdrsAB As String
Dim sAvgIncrease As String
Dim sCasualAvgIncrease As String

'Find the last Row based on column AB.
lLastRow = Range("AB" & CStr(Rows.Count)).End(xlUp).Row

'Make addresses for ranges.
'FormulaArray always uses R1C1 reference style, not the A1 reference
style.
'So the addresses are in (absolute) R1C1 style.
sAdrsN = Range("N1:N" & CStr(lLastRow)).Address(True, True, xlR1C1)
sAdrsP = Range("P1" & CStr(lLastRow)).Address(True, True, xlR1C1)
sAdrsAB = Range("AB1:AB" & CStr(lLastRow)).Address(True, True, xlR1C1)

'Create Average formula strings.
sAvgIncrease = "=AVERAGE(IF((" & sAdrsN & ">0)*(" & sAdrsP & "<>110)," &
sAdrsAB & "))"
sCasualAvgIncrease = "=AVERAGE(IF((" & sAdrsN & "=0)*(" & sAdrsP &
"<>110)," & sAdrsAB & "))"

'Now insert formulas; BRACKETS INSERTED BY EXCEL!
Range("AB" & CStr(lLastRow + 1)).FormulaArray = sAvgIncrease
Range("AB" & CStr(lLastRow + 2)).FormulaArray = sCasualAvgIncrease

End Sub

If MSP = Member of the Scottish Parliament, you owe me a wee dram.

Regards
Paul