[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 680
  • Last Modified:

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.  
0
daintysally
Asked:
daintysally
  • 7
  • 5
  • 2
  • +2
1 Solution
 
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
 
SiddharthRoutCommented:
Try this

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

Sid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
SiddharthRoutCommented:
Oops

Try this

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

Sid
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now