change zero

[tintin1012000]

Hello,

I want to change a cell value from zero to 0.01 when zero is typed in.
I need this to happen automatically

Anyone know

 

[gaj104]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Selection, Target) Is Nothing Then
    If Target.Value = 0 Then Target.Value = 0.1
End If

End Sub

 

[Legacy 167133]

gaj 104 gave you good code, you may wnt to change the line - If Target.Value = 0 Then Target.Value = 0.1 to - If Target.Value = 0 Then Target.Value = 0.01
to get the desired 0.01 result.

Good job gaj 104 !
I'll make a note of your website

 

[Peter_SSs]

Try

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Selection, Target) Is Nothing Then
    If Target.Value = 0 Then Target.Value = 0.1
End If

End Sub

As I understand the requirement, that code is not very robust. Try each of these ..

1. Select a cell, type a 0 and instead of confirming with the Enter key, use the mouse to click the tick mark just to the left of the formula bar. (The same thing would result if the user has disabled the option to "After pressing Enter, move selection ..")

2. Select a group of cells, type a 0 and confirm with Ctrl+Enter, not just Enter.

3. Copy a zero cell (say from 1 above) and paste elsewhere.

4. Enter a 2 in a cell then re-select the cell and press F2 to edit. Now backspace to remove the 2 from the formula bar and press enter to confirm a blank (not 0) cell.


This would be my alternative ..

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range

    Application.EnableEvents = False
    For Each c In Target.Cells
        If c.Value = 0 And Len(c.Value) > 0 Then
            c.Value = 0.01
        End If
    Next c
    Application.EnableEvents = True
End Sub

BTW, in case you need to know how to implement this ..

a. Right click the sheet name tab and choose "View Code".

b. Copy and Paste the code above into the main right hand pane that opens at step a.

c. Close the Visual Basic window.

d. Try various data entry/edit/delete options.

 

[Legacy 167133]

Peter SSs,

And what would an ignorant poor mortal soul like I do, without someone like you around to make sure I always keep my eyes wide shut?

 

[tintin1012000]

Worked perfect, thank you for your help