Division by 0 error on Worksheet_Change code

tmpollard · Mar 31st 2016

Hello,
Hoping I can get some help with a problem I'm having, below is a snipit of code from my worksheet_change code. The Case numbers reference columns R and S on my spreadsheet. I am doing simple division, S/R and post answer in Column Q. It works fine in I enter data in column R then Column S but If I enter data in Column S first I get a division by 0 error. I would like to prevent this as the end user of the spreadsheet may enter the values in any order.

Can someone please help. Thanks

Code

Case Is = 19
 If Target.Value <> "" Then
 Target.Offset(, -2) = Target / Target.Offset(, -1)
 End If
 
 Case Is = 18
 If Target.Value <> "" Then
 Target.Offset(, -1) = Target.Offset(, 1) / Target
 End If

skywriter · Apr 1st 2016

Re: Division by 0 error on Worksheet_Change code

Maybe this. :cool:

Code

Private Sub WorkSheet_Change(ByVal Target As Range)
Select Case Target.Column
   Case Is = 19
    If Target.Value <> "" And Target.Offset(, -1) <> "" And Target.Offset(, -1) <> 0 Then
    Application.EnableEvents = False
        Target.Offset(, -2) = Target / Target.Offset(, -1)
    Application.EnableEvents = True
    End If


Case Is = 18
    If Target.Value <> "" And Target.Value <> 0 Then
    Application.EnableEvents = False
        Target.Offset(, -1) = Target.Offset(, 1) / Target
    Application.EnableEvents = True
    End If
End Select
End Sub

Display More

Division by 0 error on Worksheet_Change code

Participate now!

Share