[Solved] Error "Divide by Zero"

[mfaisal.ce]

Dear,

I am doing certain calcuations in vba script but getting error "Divide by Zero". I tried to use Worksheetfunction.Iferror but no use.
Any one can guide me how to encounter this problem?

Scenario is as;
I have two Columns A & B; Value of A is divided by value of B but when Value of B is 0, this error appears. how to solve this error within vba script body ?

Regards,

 

[Rubayed Razib]

there are numerous ways you can avoid this error. The best way may be to use the "On Error Resume Next" command right before the line that might involve an operation that can output the Error. We provided an example code with the dataset according to your scenario.

Sub AvoidDivideByZero()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet3")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow
        On Error Resume Next
        Dim dividend As Double
        Dim divisor As Double
        Dim result As Double
        dividend = ws.Cells(i, "A").Value
        divisor = ws.Cells(i, "B").Value
        result = dividend / divisor
        If Err.Number <> 0 Then
            ws.Cells(i, "C").Value = "Error: Divide by zero is not allowed."
            Err.Clear
        Else
            ws.Cells(i, "C").Value = result
        End If
    Next i
End Sub

here we have the values in the A1:A7 cells and these values are going to be divided by the values in the range B3:B7. We can see that there is a value in the cell B4 which is going to make a divisional error. the modification in the code will escape the error and execute the rest of the operation. you can put any messge in the error identification part. You also can simply keep the cell empty.

 

[mfaisal.ce]

there are numerous ways you can avoid this error. The best way may be to use the "On Error Resume Next" command right before the line that might involve an operation that can output the Error. We provided an example code with the dataset according to your scenario.

Sub AvoidDivideByZero()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet3")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow
        On Error Resume Next
        Dim dividend As Double
        Dim divisor As Double
        Dim result As Double
        dividend = ws.Cells(i, "A").Value
        divisor = ws.Cells(i, "B").Value
        result = dividend / divisor
        If Err.Number <> 0 Then
            ws.Cells(i, "C").Value = "Error: Divide by zero is not allowed."
            Err.Clear
        Else
            ws.Cells(i, "C").Value = result
        End If
    Next i
End Sub

View attachment 362
here we have the values in the A1:A7 cells and these values are going to be divided by the values in the range B3:B7. We can see that there is a value in the cell B4 which is going to make a divisional error. the modification in the code will escape the error and execute the rest of the operation. you can put any messge in the error identification part. You also can simply keep the cell empty.

Dear,
Thanks for the help.
How to use worksheetfunction.iferror function inside vb script or worksheetfunction.iif function. I want to use any of above because both are aingle line functions. Can u plz guide me if i can use anyone of above, it will be a great help.

Regards,

 

[Rubayed Razib]

Greetings mFaisal,
Actually, in the VBA script, the IFERROR function can't handle the runtime error like DIV/0. It will work as a normal worksheet function in the workbook, but not in the VBA script as you have shown. So that is why you are going to need the workaround. As you mentioned earlier, you can use the below one-liner codec command to execute the operation. I also made sure the code remains small for you.

Sub AvoidDivideByZero()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet3")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim rangeWithData As Range
    Set rangeWithData = ws.Range("A1:B" & lastRow)
    On Error Resume Next
    ws.Range("C1:C" & lastRow).Formula = "=IFERROR(A1/B1, ""Error: Divide by zero is not allowed."")"
    On Error GoTo 0
    ws.Range("C1:C" & lastRow).Value = ws.Range("C1:C" & lastRow).Value
End Sub

If you have any more query, don't hesitate to ask.

 

[mfaisal.ce]

Greetings mFaisal,
Actually, in the VBA script, the IFERROR function can't handle the runtime error like DIV/0. It will work as a normal worksheet function in the workbook, but not in the VBA script as you have shown. So that is why you are going to need the workaround. As you mentioned earlier, you can use the below one-liner codec command to execute the operation. I also made sure the code remains small for you.

Sub AvoidDivideByZero()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet3")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim rangeWithData As Range
    Set rangeWithData = ws.Range("A1:B" & lastRow)
    On Error Resume Next
    ws.Range("C1:C" & lastRow).Formula = "=IFERROR(A1/B1, ""Error: Divide by zero is not allowed."")"
    On Error GoTo 0
    ws.Range("C1:C" & lastRow).Value = ws.Range("C1:C" & lastRow).Value
End Sub

If you have any more query, don't hesitate to ask.

Dear,

WORKSHEETFUNCTION.IIF(iserror(statement),"Error","Clear")

Above statement is also not working in vb script, any clue.

Regards,

 

[Rubayed Razib]

Greetings mFaisal,
The WORKSHEETFUNCTION.IIF(iserror(statement),"Error","Clear") can not handle the division by zero error. It can only handle some specific types of errors. I prefer you to stick with the one liner solution provided above.

Regards
Ruabayed Razib
Exceldemy Team

 

[mfaisal.ce]

Ok bro

Thanks