asked on

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.

barry houdini

To return a blank instead

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

regards, barry

jppinto

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

SiddharthRout

Try this

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

Sid

SiddharthRout

Toooooooooo Slow!

Sid

jppinto

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

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

jppinto

daintysally

ASKER

I have tried all of these formulas and it runs entirely tooooooooo slow. Any other ideas?

SiddharthRout

Can you share the code for the function?

Sid

daintysally

ASKER

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

SiddharthRout

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

daintysally

ASKER

Sid,

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

Compile Error:

Wrong number of arguments or invalid property assignment

SiddharthRout

Which line gives you the error?

Sid

daintysally

ASKER

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

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

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

daintysally

ASKER

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!!

SiddharthRout

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