replace zero for minus values by cell format if it's possible

[Abdo]

Hi

I no know if it's possible do that without macro . is it possible by cell format

so I have minus values in column E I want showing zero . the column E could be numbers or formula . if it's not possible, no problem by vba

but I want showing zero in column E when write numbers in column C or column D directly not every time I have to press button the macro to show zero .
thanks

 

[RobP]

Can you share how you get your minus values in Col E (is there a formula in there already, that we could manipulate to help you ?)

(Is Col E acting on the numbers you put in Col C and D for example ?)

thansk
rob

 

[Abdo]

here is structure my data

st.xlsm
	C	D	E
1	IMPORT	EXPORT	BALANCE
2	11.00	111.00	-100.00
3	5.00	10.00	-5.00
4	22.00	2.00	20.00
5	3.00	2.00	1.00
SS

Cell Formulas
Range	Formula
E2:E5	E2	=C2-D2

 

[Peter_SSs]

Could you just use this formula instead?

22 09 05.xlsm
	C	D	E
1	IMPORT	EXPORT	BALANCE
2	11	111	0
3	5	10	0
4	22	2	20
5	3	2	1
No neg

Cell Formulas
Range	Formula
E2:E5	E2	=MAX(C2-D2,0)

 

[Abdo]

@Peter_SSs thanks , actually this formula is hidden in column E by another macro to subtract the column C from column D

the macro call from sheet change event when add in column C or D to subtracting . so I want adding to sheet change event how I make zero for minus values in column E when show the values after calculation.

 

[Peter_SSs]

Perhaps you could post your macro code?

BTW, if you already have a macro in getting the current result, why are you asking to do the next step without a macro?

 

[Abdo]

Perhaps you could post your macro code?

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Check to see if updated cell is in columns C or D after row 2
    If (Target.Column = 3 Or Target.Column = 4) And (Target.Row > 1) Then
        Application.EnableEvents = False
        Call test
        Application.EnableEvents = True
    End If

End Sub

Sub test()

    Dim LastRow As Long
    Dim rng As Range
    last_row = ThisWorkbook.Worksheets("it").Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("E2:E" & last_row)
    rng.Formula = "=C2-D2"
    rng.Value = rng.Value

End Sub

why are you asking to do the next step without a macro?

the main reason to avoid overlaping codes each other of them can cause error or become heavy file . I see in some cases just simple formula in cell format can do that , so why I should use macro if I have macro?

 

[MEUserII]

@Peter_SSs thanks , actually this formula is hidden in column E by another macro to subtract the column C from column D

the macro call from sheet change event when add in column C or D to subtracting . so I want adding to sheet change event how I make zero for minus values in column E when show the values after calculation.

Update your VBA code to the following:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Check to see if updated cell is in columns C or D after row 2
    If (Target.Column = 3 Or Target.Column = 4) And (Target.Row > 1) Then
        Application.EnableEvents = False
        Call test
        Application.EnableEvents = True
    End If

End Sub

Sub test()

    Dim LastRow As Long
    Dim rng As Range
    last_row = ThisWorkbook.Worksheets("it").Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("E2:E" & last_row)
    rng.Formula = "=MAX((C2-D2), (0) )"
    rng.Value = rng.Value

End Sub

Since you describe your code based on sheet change event trigger; then this VBA code change will always ensure the formula entered in column E, courtesy of @Peter_SSs, always returns positive values in column E (as long as Excel formula calculation is set to automatic).

 

[Abdo]

@MEUserII thank you dor updating
@Peter_SSs thanks for your formula

​

thanks all

 

[Peter_SSs]

the main reason to avoid overlaping codes each other of them can cause error or become heavy file

As you have seen, there is no need for overlapping codes and nothing in the suggestion should make your file 'heavy'.
I do have some comments and suggestions for what I believe are improvements.

1. I don't see any reason for exiting if more that one cell is changed at a time. If the user, for example, deleted two or more existing values from column C then the code will leave incorrect values in column E.
   
   
2. Your code, and the accepted suggestion in post #8 both have Dim LastRow As Long but then proceed to calculate a value for a different, undeclared, variable name last_row. This indicates that you do not have Option Explicit set at the top of the module. I would strongly recommend that you always do. That is easily automatically done by a one-off process in the vba window of Tools - Options... - Editor tab - Require Variable Declaration.
   
   
   
   
   
   If you don't have that setting, somewhere along the line you are going to accidentally mis-spell a variable name and it will not be holding the value that you think you have set. The result is that the code may error or cause an obvious mistake which you can then search for, but it may also result in an incorrect result that you do not notice is incorrect.
   As it turns out, I don't think you even need the variable for the last row - see my suggested code below.
   
   
3. It would appear that this Worksheet_Change code is in the "it" sheet's module. If that is so, there is no need to specify the sheet in the 'test' procedure.
   
   
4. It is not a big deal, but the formula entered in the post 8 code has two sets of parentheses that are not required.

Putting all of the above together, this is my suggestion for a simpler, better, more efficient code is:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Check to see if anything changed in columns C or D
    If Not Intersect(Target, Columns("C:D")) Is Nothing Then
        Application.EnableEvents = False
        Call test
        Application.EnableEvents = True
    End If
End Sub

Sub test()
  With Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Formula = "=MAX(C2-D2,0)"
    .Value = .Value
  End With
End Sub

Unless you are intending to do other things as well with the Worksheet_Change code you could just put all the above together in the Worksheet_Change code itself without the need for the separate 'test' procedure.

Private Sub Worksheet_Change(ByVal Target As Range)
  'Check to see if anything changed in columns C or D
  If Not Intersect(Target, Columns("C:D")) Is Nothing Then
    Application.EnableEvents = False
    With Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
      .Formula = "=MAX(C2-D2,0)"
      .Value = .Value
    End With
    Application.EnableEvents = True
  End If
End Sub