CODEWITHSUNDEEP
vbaexcelexcel-formula
rebecca_m_wonk
rebecca_m_wonk

Reputation: 21

Find average of several cells then autofill

I am looking to find the average of cells A2:D2 and place them into E2 then auto fill that equation into the rest of the 500 or so E cells. Here is what I have so far:

Sub average ()
Dim nRows As Integer
Dim nCols As Integer

nCols = Range(Range("A2"), Range("A2").End(xlToRight)).Columns.Count
nRows = Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count
Range("E2").FormulaR1C1 = "=AVERAGE(R[]C[ " & nCols & "]:R[]C[])"

End Sub

Upvotes: 1

Views: 58

Answers (1)

Joe W
Joe W

Reputation: 111

The Macro below will put the average of A to D in column E for each row and pull the formula down to the last row of your worksheet. 

Sub Average()

Dim lRow As Long
Dim ACell As String
Dim Col As String

    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-1])"
    lRow = WorksheetFunction.Max(Range("A65536").End(xlUp).Row, 
    Range("B65536").End(xlUp).Row, Range("C65536").End(xlUp).Row)
    ACell = Range("E2").Address(RowAbsolute:=False, ColumnAbsolute:=False)
    Col = Left(ACell, 1)
    Range(ACell & ":" & ACell).AutoFill Range(ACell & ":" & Col & lRow)

End Sub

Upvotes: 1

Related Questions