Using IFERROR in VBA

Question

I hope someone can help me please? I've done a lot of googling and can't figure out what the issue is. I only dabble in vba, so I'm certainly no expert...

I am trying to automate some calculations in a huge spreadsheet at work, and I think I'm probably missing something really silly. Basically, one of the calculations is a simple divide of one cell by another cell. When it hits an error, I want it to return a 0. Here is the code that keeps tripping over:

Sheets("Bridge").Range("W" & SumIfInt) = Application.WorksheetFunction.IfError(Sheets("Bridge").Range("AA" & SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt), 0)

I get a Run-time error 6 Overflow

Thanks in advance

Answer 1

When programming it's always best to avoid an error by checking for it's possibility first, rather than triggering one then dealing with it. Check if the cell is empty or zero before performing the calculation:

If IsEmpty(Sheets("Bridge").Range("D" & SumIfInt)) Or Sheets("Bridge").Range("D" & SumIfInt) = 0 Then
    Sheets("Bridge").Range("W" & SumIfInt) = 0
Else
    Sheets("Bridge").Range("W" & SumIfInt) = Sheets("Bridge").Range("AA" & SumIfInt) / Sheets("Bridge").Range("D" & SumIfInt)
End If

On Error Resume Next basically says "If you encounter an error just ignore it". This can cause all sorts of unexpected problems and should only be used as a last resort.

Also check out the IsError function in VBA.