We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Get rid of #div/0! error within an average calculation in excel vba

Medium Priority
879 Views
Last Modified: 2012-06-21
Hi Experts,

Can someone quickly tell me how to enhance this formula in vba to get rid of the #DIV/0! error in my excel workbook?  The formula is:

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:RC[-1])

This formula is included in a function within the excel vba code.  
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
To return a blank instead

ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"

regards, barry

Commented:
ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"")
Try this

ActiveCell.FormulaR1C1 = "=IF(ISERROR(AVERAGE(RC[-12]:RC[-1])),"""",AVERAGE(RC[-12]:RC[-1]))"

Sid
Toooooooooo Slow!

Sid

Commented:
Got an error because of the double quotes! It should be like Barry posted:

ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"

jppinto

Author

Commented:
I have tried all of these formulas and it runs entirely tooooooooo slow.  Any other ideas?
Can you share the code for the function?

Sid

Author

Commented:
Sure, actually, it is a macro that was copied and pasted.  See below:

Dim i As Integer

For i = 3 To Range("a2000").End(xlUp).row
   
    Range("t" & i).Activate
   
    ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
                Selection.NumberFormat = "0.00"
   
   
    Range("ah" & i).Activate
    ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
                Selection.NumberFormat = "0.00"
   
   
    Range("av" & i).Activate
    ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
                Selection.NumberFormat = "0.00"
   
   
    Next i
   
    Calculate
Try this

Sub Sample()
    Dim i As Integer
    
    Application.Calculation = xlCalculationManual
    
    For i = 3 To Range("a2000").End(xlUp).Row
        Range("t" & i).FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
        Range("ah" & i).FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
        Range("av" & i).FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
        Range("T" & i, "AH" & i, "AV" & i).NumberFormat = "0.00"
    Next i
   
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window


Sid

Author

Commented:
Sid,

When I try your code, I get the following error:

Compile Error:

Wrong number of arguments or invalid property assignment
Which line gives you the error?

Sid

Author

Commented:
Range("T" & i, "AH" & i, "AV" & i).NumberFormat = "0.00"
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Rather than looping, I'd use:
Dim lngLastRow As Long

lngLastRow = Range("a2000").End(xlUp).Row
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    With Range("AH3")
        .FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
        .NumberFormat = "0.00"
    End With
    Range("T3:T" & lngLastRow).FillDown
    With Range("T3")
        .FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
        .NumberFormat = "0.00"
    End With
    Range("AH3:AH" & lngLastRow).FillDown
    With Range("AV3")
        .FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"""")"
        .NumberFormat = "0.00"
    End With
    Range("AV3:AV" & lngLastRow).FillDown
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

Open in new window

Author

Commented:
Thank you Sid!!! Works like a charm.  Now I need to figure out how to append records to the data that I am copying to another worksheet within the same excel workbook.  I will open another question for that, I would appreciate your help!!
daintysally: I believe you should have accepted Barry's solution and mine as 'Assist' as he originally answered your main question about the #DIV/0!

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.