IFERROR code in VBA

[SaraB802]

I have written some code to extract data from one sheet to another, and one of the formulas is dividing 2 cells, when there is a 0 value in either I get a #DIV/0! error, what I would like is blank.

the last section of the code is:

Range("C53").Select
ActiveCell.FormulaR1C1 = "=R[-2]C/R[-7]C"
'
Range("C55").FormulaArray = "=INDEX('Weekly Operating Report Summ'!AJ:AK,MATCH(A55,'Weekly Operating Report Summ'!B:B,0),2)"
'
Range("C56").FormulaArray = "=INDEX('Weekly Operating Report Summ'!AJ:AK,MATCH(A56,'Weekly Operating Report Summ'!B:B,0),2)"
'
Range("C57").FormulaArray = "=INDEX('Weekly Operating Report Summ'!AJ:AK,MATCH(A57,'Weekly Operating Report Summ'!B:B,0),2)"
'
Range("C58").FormulaArray = "=INDEX('Weekly Operating Report Summ'!AJ:AK,MATCH(A58,'Weekly Operating Report Summ'!B:B,0),2)"
'
Range("C59").FormulaArray = "=INDEX('Weekly Operating Report Summ'!AJ:AK,MATCH(A59,'Weekly Operating Report Summ'!B:B,0),2)"
'
Range("C60").FormulaArray = "=INDEX('Weekly Operating Report Summ'!AJ:AK,MATCH(A60,'Weekly Operating Report Summ'!B:B,0),2)"
'
Range("C61").FormulaArray = "=INDEX('Weekly Operating Report Summ'!AJ:AK,MATCH(A61,'Weekly Operating Report Summ'!B:B,0),2)"
'
Range("B10:C12,B31:C31,B39:C40,B53:C53,B55:C61").Select
Selection.NumberFormat = "0.0%"
Range("B20:C22,B41:C41,B43:C43,B46:C52").Select
Selection.NumberFormat = "0.0"
'
Range("B10:C61").Select

Selection.Copy


Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub


This ultimately pastes the values in removing the formaulas to keep the size of the workbook down. How can I add in the iferror function to remove the #DIV/0! that appears in row 53.

Thank you

 

[BarryL]

Re: IFERORR code in VBA

try

Range("C53").FormulaR1C1 = "=IFERROR(R[-2]C/R[-7]C,"""")"

 

[SaraB802]

Re: IFERORR code in VBA

BarryL

thank you that works perfectly!, Now I just have to change 106 macros to reflect the change!!

 

[BarryL]

Re: IFERORR code in VBA

if theyre in the same modules, try ctrl+H. that's a lot of macros!