VBA for calculating Average

[Peter04]

Hi,

I have to calculate the average of each column values at sheet 2 and store it to sheet 3. While I am running the code, it ends with Runtime Error 6 Overflow. My code is given below.

Sub AvgCal()
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim count As Integer
Dim sum As Long
Dim avr As Long
Dim lstCol As Integer

lstCol = ws2.UsedRange.Columns(ws2.UsedRange.Columns.count).Column
For c = 2 To lstCol
    sum = 0
    count = 0
    ws2.Cells(1, c).Select
    Do While ActiveCell.Value <> ""
        sum = sum + ActiveCell.Value
        count = count + 1
        ActiveCell.Offset(1, 0).Activate
    Loop
     ws3.Cells(2, c).Value = sum / count 
Next c

End Sub

Could you please help me to settle this.. Thanks

 

[RoryA]

Welcome to the forum.

Since you haven't mentioned which line causes the error, I'd start by changing the Integer declarations to Long, and change the sum variable to a Double.

 

[Peter04]

Hi Rory,
Thanks for your reply.
The line "ws3.Cells(2, c).Value = sum / count" causes the error.
Even after change the 'sum' variable to 'double', still the same error is coming.
Thanks

 

[ZVI]

This can happen if sum = 0 and count = 0.
Use this modified code line:
If count > 0 Then ws3.Cells(2, c).Value = sum / count

or better:
​

    If count > 0 Then
      ws3.Cells(2, c).Value = sum / count
    Else
      ws3.Cells(2, c).ClearContents
    End If

 

[ZVI]

This is faster alterrnative code:

Sub AvgCal1()
  Dim LastCol As Long, LastRow As Long
  Dim rng As Range
  With Range(ws2.Range("A1"), ws2.UsedRange)
    LastRow = .Rows.Count
    LastCol = .Columns.Count
  End With
  Set rng = ws3.Range("B2").Resize(1, LastCol - 1)
  With rng
    .Formula = "=IFERROR(AVERAGE('" & ws2.Name & "'!B1:B" & LastRow & "),"""")"
    .Value = .Value
  End With
End Sub

 

[Peter04]

Hi ZVI,

Thanks for the help. It's perfectly working.

 

[ZVI]

Hi ZVI,

Thanks for the help. It's perfectly working.

Glad it helped, Peter!