Error Overflow in VBA

Question

I'm a newbie to VBA. Recently, I have typed some codes and following is the sample of my codes:

Dim n As Long
n = Range("A1", Range("A1").End(xlDown)).Rows.Count
For i = 3 To n
Range("P" & i).Value = WorksheetFunction.IfError(Range("N" & i).Value / Range("O" & i).Value, 0))
Next

And it turns out to have the error of Overflow. I have searched on the Internet and figure out it my sample code should be converted to Long type data. However, when I change into:

Range("P" & i).Value = CLng(WorksheetFunction.IfError(CLng(Range("N" & i).Value) / CLng(Range("O" & i).Value), 0))

the problem also remains.

Thank you for any help !

Answer 1

The division in your code (Range("N" & i).Value / Range("O" & i).Value) is happening before it is passed as a parameter to the IfError function. Therefore, if the division fails, your code crashes and the IfError never gets a chance to do anything.

An alternate way of doing this would be:

Dim n As Long
n = Range("A1", Range("A1").End(xlDown)).Rows.Count
For i = 3 To n
    'Set the value in column P to a default value
    Range("P" & i).Value = 0
    'Switch on error handling
    On Error Resume Next
    'Attempt the calculation - if it fails, the value in column P will not change
    Range("P" & i).Value = Range("N" & i).Value / Range("O" & i).Value
    'Switch error handling off again
    On Error GoTo 0
Next