CODEWITHSUNDEEP
excelsumrowaveragevba
Mary
Mary

Reputation: 788

Average rows of variable length VBA

I am trying to get a simple VBA code as part of a Macro that will put in column B the average of the values for each row. I have code that generates a time series and fills a column per simulation such that each column is a time series starting at column C. The number of simulations vary so I simply need something that averages the value for each point in time (ie for each row across all simulations) whilst adjusting for the number of simulations that are run (columns that are filled). I would then like it to generate a single graph of all the time series highlighting the average values that are calculated. Many thanks if you can help!

Here, for example, is the code that takes the values for the time steps from sheet1 and places it in columnA sheet2. I would like the macro to now place the average in the appropriate row down Column B: Screenshot Of current output

Sheets("Sheet1").Select
Range("E5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Upvotes: 1

Views: 13004

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Try this

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("B2:B" & lRow).Formula = _
        "=IF(ISERROR(AVERAGE(C2:E2)),"""",AVERAGE(C2:E2))"
    End With
End Sub

FOLLOWUP

Try this

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long
    Dim ColNm As String

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        lCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        ColNm = Split(.Cells(, lCol).Address, "$")(1)

        .Range("B2:B" & lRow).Formula = "=IF(ISERROR(AVERAGE(C2:" _
                                        & ColNm & _
                                        "2)),"""",AVERAGE(C2:" & _
                                        ColNm & "2))"
    End With
End Sub

Upvotes: 1

whytheq
whytheq

Reputation: 35605

Try this although there will be lots of different variants on this code:

Sub AddAvgFormula()

With Sheets("Sheet2")
        .Range("B2").FormulaR1C1 = "=AVERAGE(RC[1]:RC[3])"
        .Range("B2").Copy
        .Range("B2:B" & .Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row).PasteSpecial Excel.xlPasteFormulas
End With

End Sub

At the end of your current code before the End Sub add this:

Call AddAvgFormula

Here's a varient of the original code:

Sub AddAvgFormula()

With Sheets("Sheet2")
        .Range("B2") = "=AVERAGE(C2:E2)"
        .Range("B2").Copy
        .Range("B2:B" & .Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row).PasteSpecial Excel.xlPasteFormulas
End With

End Sub

Another variant which is shorter but maybe not so intuitive:

Sub AddAvgFormula()

With Sheets("Sheet2")
        .Range("B2:B" & .Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row) = "=AVERAGE(C2:E2)"
End With

End Sub

Upvotes: 1

Related Questions