<div>
Try:<br />
<br />
&nbsp; &nbsp; Sub RangeAvg()<br />
&nbsp; &nbsp; Dim i as Integer, ShadeAvg as Integer<br />
&nbsp; &nbsp; Dim JanArray(1 To 28) As Double<br />
<br />
&nbsp; &nbsp; For i = 3 To 57 Step 2<br />
&nbsp; &nbsp; &nbsp; &nbsp; ShadeAvg = Application.Average(Range(<wbr />Cells(16, i), Cells(46, i + 1)))<br />
&nbsp; &nbsp; &nbsp; &nbsp; JanArray((i - 1) \ 2) = ShadeAvg<br />
&nbsp; &nbsp; Next i<br />
<br />
&nbsp; &nbsp; Range(&quot;d4:ae4&quot;).Value = JanArray<br />
<br />
&nbsp; &nbsp;End Sub<br />
<br />
Kevin
</div>


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

<div>
Kevin,<br />
You are the man.<br />
Thank you!<br />
-Michael
</div>


Kevin,
You are the man.
Thank you!
-Michael

<div>
<div class="content wysiwyg-content">
Hi,<br />
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 &quot;#DIV/0!&quot;. What's the best way to make VBE give those ranges a &quot;0&quot; value, rather than a &quot;#DIV/0!&quot; value?<br />
<br />
Here's the code format:<br />
<br />
&nbsp; &nbsp; Sub RangeAvg()<br />
&nbsp; &nbsp; Dim i as Integer, ShadeAvg as Integer, JanArray<br />
<br />
&nbsp; &nbsp; For i = 3 To 57 Step 2<br />
&nbsp; &nbsp; &nbsp; &nbsp; ShadeAvg = Application.Average(Range(<wbr />Cells(16, i), Cells(46, i + 1)))<br />
&nbsp; &nbsp; &nbsp; &nbsp; JanArray((i - 1) / 2) = ShadeAvg<br />
&nbsp; &nbsp; Next i<br />
<br />
&nbsp; &nbsp; Range(&quot;d4:ae4&quot;).Value = JanArray()<br />
<br />
&nbsp; &nbsp;End Sub<br />
<br />
Thanks!<br />
-Michael
</div>
</div>


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

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

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.

VB Script

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.