Run Time Error 6 Division by 0 but its not trying to divide by 0

[Goneill]

Hello,

I am very new to writing VBA code.I have the code below and for some reason keep getting run time error 6 "division by 0". The values in the cells i am trying to divide do not have a zero so i am very puzzled. The division by zero error only happens in my "Cloud PBX Voice Services (MRC - Monthly changers)" section. but in the other 4 sections I get a runtime error 11 "overflow" I have searched for days to find why this is happening but have found nothing. Any help is Greatly appriciated.

Also quckly want to add that the macro does actualy perform all the math and place it on the sheet even tho the error is there.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

r = Target.Row
c = Target.Column
Value = ActiveSheet.Cells(r, c)
rw = r
If c = 3 Then
    Exit Sub
End If
If c = 1 Then
    

    If ActiveSheet.Cells(r, c + 1).Interior.Color = 16776960 Or ActiveSheet.Cells(r, c + 1).Interior.Color = 16776961 Then
        
       Sheets("Work Sheet").Cells(r, 7) = Sheets("Work Sheet").Cells(r, 1) * Sheets("Work Sheet").Cells(r, 6)
       Sheets("Work Sheet").Cells(r, 9) = Sheets("Work Sheet").Cells(r, 6) - Sheets("Work Sheet").Cells(r, 8)
       Sheets("Work Sheet").Cells(r, 10) = Sheets("Work Sheet").Cells(r, 9) / Sheets("Work Sheet").Cells(r, 6)
       Call total(rw)
       Call total_chg
    End If

End If

If c = 6 Then

    If ActiveSheet.Cells(r, c - 5).Interior.Color = 16776960 Or ActiveSheet.Cells(r, c - 5).Interior.Color = 16776961 Then
        
       Sheets("Work Sheet").Cells(r, 7) = Sheets("Work Sheet").Cells(r, 1) * Sheets("Work Sheet").Cells(r, 6)
       Sheets("Work Sheet").Cells(r, 9) = Sheets("Work Sheet").Cells(r, 6) - Sheets("Work Sheet").Cells(r, 8)
       Sheets("Work Sheet").Cells(r, 10) = Sheets("Work Sheet").Cells(r, 9) / Sheets("Work Sheet").Cells(r, 6)  'This line is highlighted when i debug
       Call total(rw)
       Call total_chg
    End If

End If

If ActiveSheet.Cells(r, c).Interior.Color = 16776960 Or ActiveSheet.Cells(r, c).Interior.Color = 16776961 Then

    For i = 2 To 100
    
        If ActiveSheet.Cells(r - 1, 1).Interior.Color = 192 Then

            If ActiveSheet.Cells(r - 1, 1) = "Hardware and Software  (NRC / One Time Cost)" Then
                
                sheetname = "Hardware Software"
                
                Exit For
                
            End If
            If ActiveSheet.Cells(r - 1, 1) = "Cloud PBX Voice Services (NRC / One Time Cost)" Then
            
                sheetname = "Voice Services NRC"
                Exit For
                
            End If
            If ActiveSheet.Cells(r - 1, 1) = "Cloud PBX Voice Services (MRC - Monthly Charges)" Then
            
                sheetname = "Voice Services MRC"
               
                Exit For
                
            End If
            If ActiveSheet.Cells(r - 1, 1) = "Professional Services  (NRC / One Time Cost)" Then
            
                sheetname = "professional service"
                
                Exit For
            End If
            If ActiveSheet.Cells(r - 1, 1) = " Service Level Agreement (SLA)  / Maintenance  (NRC / One Time Cost)" Then
            
                sheetname = "SLA_Maint"
                
                Exit For
                
            End If
            
        End If
        r = r - 1
        
    Next

    For j = 2 To 10000
        If Trim(LCase(Sheets(sheetname).Cells(j, 2))) = Trim(LCase(Value)) Then
     
            Sheets("Work Sheet").Cells(rw, 6) = Sheets(sheetname).Cells(j, 8)
            Sheets("Work Sheet").Cells(rw, 3) = Sheets(sheetname).Cells(j, 5)
            Sheets("Work Sheet").Cells(rw, 8) = Sheets(sheetname).Cells(j, 9)
            Sheets("Work Sheet").Cells(rw, 1) = 1
            Sheets("Work Sheet").Cells(rw, 7) = Sheets("Work Sheet").Cells(rw, 1) * Sheets("Work Sheet").Cells(rw, 6)
            Sheets("Work Sheet").Cells(rw, 9) = Sheets("Work Sheet").Cells(rw, 6) - Sheets("Work Sheet").Cells(rw, 8)
            Sheets("Work Sheet").Cells(rw, 10) = Sheets("Work Sheet").Cells(rw, 9) / Sheets("Work Sheet").Cells(rw, 6)
            Sheets("Work Sheet").Rows(rw + 1).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            
            Exit For
        End If
    
    Next

Call total(rw)
Call total_chg

End If

End Sub

 

[Goneill]

ok since neither of those are effecting column 1 or 6, you will probably not have problems caused by reentry to the event procedure.

I was able to fix the error with the code below. Thanks again for all the input!

If c = 1 Then
    

    If ActiveSheet.Cells(r, c + 1).Value > 0 Then
        
       Sheets("Work Sheet").Cells(r, 7) = Sheets("Work Sheet").Cells(r, 1) * Sheets("Work Sheet").Cells(r, 6)
       Sheets("Work Sheet").Cells(r, 10) = Sheets("Work Sheet").Cells(r, 6) - Sheets("Work Sheet").Cells(r, 8)
       Sheets("Work Sheet").Cells(r, 9) = Sheets("Work Sheet").Cells(r, 1) * Sheets("Work Sheet").Cells(r, 10)
       Sheets("Work Sheet").Cells(r, 11) = Sheets("Work Sheet").Cells(r, 10) / Sheets("Work Sheet").Cells(r, 6)
       Call total(rw)
       Call total_chg
    End If

End If

If c = 6 Then

    If ActiveSheet.Cells(r, c - 5).Value > 0 Then
        
       Sheets("Work Sheet").Cells(r, 7) = Sheets("Work Sheet").Cells(r, 1) * Sheets("Work Sheet").Cells(r, 6)
       Sheets("Work Sheet").Cells(r, 10) = Sheets("Work Sheet").Cells(r, 6) - Sheets("Work Sheet").Cells(r, 8)
       Sheets("Work Sheet").Cells(r, 9) = Sheets("Work Sheet").Cells(r, 1) * Sheets("Work Sheet").Cells(r, 10)
       Sheets("Work Sheet").Cells(r, 11) = Sheets("Work Sheet").Cells(r, 10) / Sheets("Work Sheet").Cells(r, 6)
       Call total(rw)
       Call total_chg
    End If

End If

 

[par60056]

glad you got it working. It seemed like you would be pretty close and there were a couple ways to fix it. glad you found 1.

 

[Norie]

Have a look at using this

 Application.EnableEvents = False

That will stop the code calling itself.

 

[par60056]

The way the code is written, it looks like it intends to call itself. It is not how I would write it. It changes values in column 1 which causes it to be called and other cells are changed because column 1 changed.

Have a look at using this

 Application.EnableEvents = False

That will stop the code calling itself.