VBA: Getting Range Averages w/o #DIV/0!

Hi,
In the VBE, I'm looping through a spreadsheet (that I can't modify) to get averages. Some ranges I'm getting averages for consist of all blank cells--so the result in those cases shows "#DIV/0!". What's the best way to make VBE give those ranges a "0" value, rather than a "#DIV/0!" value?

Here's the code format:

    Sub RangeAvg()
    Dim i as Integer, ShadeAvg as Integer, JanArray

    For i = 3 To 57 Step 2
        ShadeAvg = Application.Average(Range(Cells(16, i), Cells(46, i + 1)))
        JanArray((i - 1) / 2) = ShadeAvg
    Next i

    Range("d4:ae4").Value = JanArray()

   End Sub

Thanks!
-Michael
bishop3000Asked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
Redone to account for errors:

    Sub RangeAvg()
    Dim i As Integer
    Dim ShadeAvg As Variant
    Dim JanArray(1 To 28) As Double

    For i = 3 To 57 Step 2
        ShadeAvg = Application.Average(Range(Cells(16, i), Cells(46, i + 1)))
        If Not IsError(ShadeAvg) Then
            JanArray((i - 1) \ 2) = ShadeAvg
        End If
    Next i

    Range("d4:ae4").Value = JanArray

   End Sub

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Try:

    Sub RangeAvg()
    Dim i as Integer, ShadeAvg as Integer
    Dim JanArray(1 To 28) As Double

    For i = 3 To 57 Step 2
        ShadeAvg = Application.Average(Range(Cells(16, i), Cells(46, i + 1)))
        JanArray((i - 1) \ 2) = ShadeAvg
    Next i

    Range("d4:ae4").Value = JanArray

   End Sub

Kevin
0
 
bishop3000Author Commented:
Kevin,
You are the man.
Thank you!
-Michael
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.