CODEWITHSUNDEEP
excelvbainteger-overflow
Hiếu Nguyễn Phi
Hiếu Nguyễn Phi

Reputation: 37

Error Overflow in VBA

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 !

Upvotes: 0

Views: 751

Answers (2)

Mrig
Mrig

Reputation: 11702

You can check whether the cell value is zero or null. If not you can perform your caluculation.

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

Public Function NotNullOrZero(aValue As Variant) As Boolean
    ' Returns true if the value is not null and greater than zero
    If Not IsNull(aValue) Then
       If (aValue > 0) Then
           NotNullOrZero = True
       End If
    End If
    NotNullOrZero = False
End Function

Got NotNullOrZero function from here answered by @BrianKE.

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 2

Related Questions