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

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.  
daintysallyAsked:
Who is Participating?
 
SiddharthRoutCommented:
Oops

Try this

Range("T" & i & ",AH" & i & ",AV" & i).NumberFormat = "0.00"

Sid
0
 
barry houdiniCommented:
To return a blank instead

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

regards, barry
0
 
jppintoCommented:
ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(RC[-12]:RC[-1]),"")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SiddharthRoutCommented:
Try this

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

Sid
0
 
SiddharthRoutCommented:
Toooooooooo Slow!

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

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

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

Sid
0
 
daintysallyAuthor 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
0
 
SiddharthRoutCommented:
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
0
 
daintysallyAuthor Commented:
Sid,

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

Compile Error:

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

Sid
0
 
daintysallyAuthor Commented:
Range("T" & i, "AH" & i, "AV" & i).NumberFormat = "0.00"
0
 
Rory ArchibaldCommented:
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

0
 
daintysallyAuthor 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!!
0
 
SiddharthRoutCommented:
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
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.