?
Solved

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

Posted on 2007-10-08
3
Medium Priority
?
443 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:bishop3000
  • 2
3 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 20038228
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 20038237
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
 

Author Comment

by:bishop3000
ID: 20038292
Kevin,
You are the man.
Thank you!
-Michael
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question