VBA: Getting Range Averages w/o #DIV/0!
Posted on 2007-10-08
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:
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
Range("d4:ae4").Value = JanArray()