VBA average with criteria on time formatted cells

RFLundgren · Mar 28, 2022

Hi all

I have a sheet that contains multiple columns, with one being time based and another showing status.

It has to loop through the data as the number of rows will change each month. Columns remain static.

I need to get the average of the time based cells in the column where status = Closed.

So far in my searching I have come across the code below, which works for the most part except for the error 6 overflow I am getting.

I have tried declaring as an integer and as a string, however get the same error in either case.

Example sheet

Example code

Once I have the average, I then need to divide it by 8 and place it into a cell on a different sheet.

Any help / suggestions, or even whole new code, would be most appreciated.

bebo021999 · Mar 28, 2022

I guess you were running code when active sheet was "Sheet1" (sheet result) (data is in other sheet, i.e, sheet2)
So, try to add sheet name within code, like this:

VBA Code:

Option Explicit
Sub test()
Dim Lr&, sum As Double, cell As Range
With Worksheets("Sheet2") 'working with sheet that contains data
Lr = .Cells(Rows.count, 3).End(xlUp).Row ' define last row of column C
    For Each cell In .Range("C2:C" & Lr) ' loop through each cell in column C
        If cell.Value Like "Closed" Then
            sum = sum + cell.Offset(0, 14).Value ' running sum of time stamp in column Q
        End If
    Next
        With Worksheets("Sheet1").Range("D8") ' paste value and format into cell D8 other sheet
            .Value = sum / (Lr - 1)
            .NumberFormat = "[hh]:mm:ss"
        End With
End With
End Sub

With data is in sheet2, result is in sheet1

Book2.xlsx

C

Q

1

Status

hrs

2

Closed