CODEWITHSUNDEEP
vbaexcel
overflowing
overflowing

Reputation: 13

Averaging each column of a dynamic table Excel VBA

I've just started learning VBA in the last two weeks so apologies if this is a basic oversight.

I'm trying to find the average of each column in a dynamic table and then populate these values (the averages of each unique column) into a table of averages in Sheet3. All of the other posts I've found on averaging ranges refer to a specific range e.g.("A2:A23") and would result in pasting one column's average multiple times as opposed to each column's average once. This is the code I have so far. Any feedback on where it could be improved is much appreciated. 

Sub AVERAGES()


Set s1 = ThisWorkbook.Sheets("Sheet1")
Set s3 = ThisWorkbook.Sheets("Sheet3")

j = 0

Do While s1.Range("B2").Offset(0, j).Value <> ""

    i = 0

    Do While s1.Range("B2").Offset(i, j).Value <> ""

    s1.Range("B2").Offset(i, j).Select

    i = i + 1

  Loop

Ave = Application.WorksheetFunction.Average(s1.Range(Cells(2, j), s1.Range(Cells(2, j).Offset(i, j))))

s3.Range(s3.Range("B2"), s3.Range("B2").Offset(0, j)).Value = Ave

j = j + 1

Loop

End Sub

Upvotes: 1

Views: 679

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You could use formulas directly in Excel, or you can reduce your VBA code to this:

Sub AVERAGES()
  Dim lastCol As Long
  lastCol = ThisWorkbook.Sheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column

  With ThisWorkbook.Sheets("Sheet3").Range("B2").Resize(1, lastCol - 1)
    .Formula = "=Average(Sheet1!B:B)"
    .value = .value
  End With
End Sub

Upvotes: 1

Related Questions