CODEWITHSUNDEEP
excelvbaerror-handling
shecodes
shecodes

Reputation: 111

average with all blank values error handling

I have a looping macro calculating averages of three values. In rare cases all three values might be blank, so I need the macro to then leave the cell where the average is printed blank, and continue with the next iteration of the loop.

I haven't done error handling before, so not sure the best way to go abut it. Here is my code:

Sub sumavg()
        Dim i As Long, j As Long
        With Worksheets("datasummary")
            For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row Step 5
                For j = 1 To 6
                    With .Rows(i + 2)
                        .Columns(19 + j).Value = Application.WorksheetFunction.Average(.Columns(j), .Columns(j + 5), .Columns(j + 10))
                    End With
                    With .Rows(i + 3)
                        .Columns(19 + j).Value = Application.WorksheetFunction.Average(.Columns(j), .Columns(j + 5), .Columns(j + 10))
                    End With
                Next
            Next

        End With
    End Sub

Here is a screenshot of the data

Upvotes: 2

Views: 298

Answers (2)

user3598756
user3598756

Reputation: 29421

you can call Application object's Average() function and check its result against being an error:

Sub sumavg()
    Dim i As Long, j As Long
    Dim res As Variant '<--| this will store the result of Application.Average() function

    With Worksheets("datasummary")
        For i = 1 To .Range("A" & .Rows.count).End(xlUp).row Step 5
            For j = 1 To 6
                With .Rows(i + 2)
                    res = Application.Average(.Columns(j), .Columns(j + 5), .Columns(j + 10))
                    If Not IsError(res) Then .Columns(19 + j).Value = Application.WorksheetFunction.Average(.Columns(j), .Columns(j + 5), .Columns(j + 10))
                End With
                With .Rows(i + 3)
                    res = Application.Average(.Columns(j), .Columns(j + 5), .Columns(j + 10))
                    If Not IsError(res) Then .Columns(19 + j).Value = Application.WorksheetFunction.Average(.Columns(j), .Columns(j + 5), .Columns(j + 10))
                End With
            Next
        Next

    End With
End Sub

Upvotes: 1

nightcrawler23
nightcrawler23

Reputation: 2066

You can try On Error Resume Next or simply add zeros to the values you are averaging.

Sub sumavg()
    Dim i As Long, j As Long
    With Worksheets("Sheet2")
        For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row Step 5
            For j = 1 To 6
                With .Rows(i + 2)
                    .Columns(19 + j).Value = WorksheetFunction.Average(.Columns(j) + 0, .Columns(j + 5) + 0, .Columns(j + 10) + 0)
                End With
                With .Rows(i + 3)
                    .Columns(19 + j).Value = WorksheetFunction.Average(.Columns(j) + 0, .Columns(j + 5) + 0, .Columns(j + 10) + 0)
                End With
            Next
        Next

    End With
End Sub

Upvotes: 1

Related Questions