Vba: Worksheet_Change doesn't work if I insert 0 (zero)

[Nelson78]

Hello everybody.

I've in sheet1 a Change Event in case a cell value is changed.

It seems to work well, except if I insert 0 in a cell. I mean: if I find that cell with 20 and change it with zero, nothing happens. If I insert, for instance, 0,0000000001 the change event works regularly.

The red instruction is the questioned one.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub

Dim lr As Long, lc As Long
Dim rng As Range

lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

If lr < 3 Or lc < 4 Then Exit Sub

On Error GoTo Skip

If Target.Column > 3 And Target.Row > 2 Then
    
    If Cells(Target.Row, 2) = "H" Then
        Application.EnableEvents = False
        Set rng = Range(Cells(Target.Row, 4), Cells(Target.Row, lc))
        Debug.Print rng.Address
            
        fr = Target.Row
        fc = Target.Column



        Cells(fr + 1, fc).Value = Cells(fr - 1, fc).Value / Cells(fr, fc).Value
        Cells(fr + 1, fc).NumberFormat = "0.00"
        
        [COLOR="#FF0000"][B]Cells(fr, 3).Value = Application.Sum(rng)[/B][/COLOR]
        
        Cells(fr + 1, 3).Value = Cells(fr - 1, 3).Value / Cells(fr, 3).Value
        Cells(fr + 1, 3).NumberFormat = "0.00"

        
        Dim varResult As Variant
            varResult = Application.WorksheetFunction.SumIf( _
            Arg1:=Range(Cells(433, 2), Cells(501, 2)), _
            Arg2:="H", _
            Arg3:=Range(Cells(433, fc), Cells(501, fc)))
        
        
            Cells(503, fc).Value = varResult
            
            Cells(504, fc).Value = Cells(502, fc).Value / Cells(503, fc).Value
            
            Cells(503, 3).Value = _
                Application.Sum(Range(Cells(503, 4), Cells(503, lc))) 
                
           Cells(504, 3).Value = Cells(502, 3).Value / Cells(503, 3).Value 


    End If

End If

Skip:

Application.EnableEvents = True

End Sub

Thank's.

 

[bobsan42]

Actually the problem with your code is two lines above the red one. - you try to divide by zero when you enter 0. Then the code exits and makes no changes.

[COLOR=#333333]Cells(fr + 1, fc).Value = Cells(fr - 1, fc).Value / [/COLOR][COLOR=#ff0000]Cells(fr, fc).Value[/COLOR]

Cells(fr, fc) is actually your Target cell in which you put a Zero which mathematically is impossible.

 

[Nelson78]

Actually the problem with your code is two lines above the red one. - you try to divide by zero when you enter 0. Then the code exits and makes no changes.

[COLOR=#333333]Cells(fr + 1, fc).Value = Cells(fr - 1, fc).Value / [/COLOR][COLOR=#ff0000]Cells(fr, fc).Value[/COLOR]

Cells(fr, fc) is actually your Target cell in which you put a Zero which mathematically is impossible.

Maybe neutralizing all the errors caused by dividing by zero, it could work. What do you think about?

[COLOR="#FF0000"][B]            If Cells(fr, fc).Value <> 0 Then
                Cells(fr + 1, fc).Value = Cells(fr - 1, fc).Value / Cells(fr, fc).Value
                Cells(fr + 1, fc).NumberFormat = "0.00"
            End If[/B][/COLOR]
        
[B][COLOR="#FF0000"]            If Cells(fr, fc).Value = 0 Then
                Cells(fr + 1, fc).Value = 0
                Cells(fr + 1, fc).NumberFormat = "0.00"
            End If[/COLOR][/B]
        
        Cells(fr, 3).Value = Application.Sum(rng)
        
        
[B][COLOR="#FF0000"]            If Cells(fr, 3).Value <> 0 Then
                Cells(fr + 1, 3).Value = Cells(fr - 1, 3).Value / Cells(fr, 3).Value
                Cells(fr + 1, 3).NumberFormat = "0.00"
            End If

            If Cells(fr, 3).Value = 0 Then
                Cells(fr + 1, 3).Value = 0
                Cells(fr + 1, 3).NumberFormat = "0.00"
            End If[/COLOR][/B]


        
        Dim varResult As Variant
            varResult = Application.WorksheetFunction.SumIf( _
            Arg1:=Range(Cells(433, 2), Cells(501, 2)), _
            Arg2:="H", _
            Arg3:=Range(Cells(433, fc), Cells(501, fc)))
        
        
            Cells(503, fc).Value = varResult 
            
            
            
[B][COLOR="#FF0000"] If Cells(503, fc).Value <> 0 Then Cells(504, fc).Value = Cells(502, fc).Value / Cells(503, fc).Value
 If Cells(503, fc).Value = 0 Then Cells(504, fc).Value = 0[/COLOR][/B]

            
            Cells(503, 3).Value = _
                Application.Sum(Range(Cells(503, 4), Cells(503, lc))) 'totale del totale h
                
[COLOR="#FF0000"][B] If Cells(503, 3).Value <> 0 Then Cells(504, 3).Value = Cells(502, 3).Value / Cells(503, 3).Value 'pma del totale pma
If Cells(503, 3).Value = 0 Then Cells(504, 3).Value = 0[/B][/COLOR]

 

[bobsan42]

Well, it is up to you to sort out the mathematics and the cell addresses and decide what the result must be when the value is 0.
A remark on the red lines in your code above: you do not need two IF statements to check a single value for the same condition. Best is to put it in a single (if ... then ... else ...) statement - the way you've done it still works, but is less efficient.

            If Cells(fr, 3).Value <> 0 Then
                Cells(fr + 1, 3).Value = Cells(fr - 1, 3).Value / Cells(fr, 3).Value
                Cells(fr + 1, 3).NumberFormat = "0.00"
            Else
                Cells(fr + 1, 3).Value = 0
                Cells(fr + 1, 3).NumberFormat = "0.00"
            End If