Dash if Zero VBA

[billandrew]

Good evening

Looking to insert a dash if cell value is a zero

using

With range("E2:E" & lastrow)

.numberformat = ?????
.Value = .Value

End With

 

[Tetra201]

Try

...
.NumberFormat = "General;General;-;General"
...

 

[Rick Rothstein]

Good evening

Looking to insert a dash if cell value is a zero

using

With range("E2:E" & lastrow)

.numberformat = ?????
.Value = .Value

End With

If you want to use Cell Formatting to display a dash when the cell value is 0, you would use this...

.NumberFormat = "[=0]-"

However, the .Value=.Value line of code makes it seem like you are trying to physically change the formatted dash to a real dash. If so, you cannot do it that way; rather, remove the two dotted code lines inside the With..EndWith block to this...

.Replace 0, "-", xlWhole, , , , False, False

 

[Michael M]

Maybe this

Sub MM1()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("E2:E" & lastrow)
        If c.Value = 0 Then c.Value = "-"
        c.HorizontalAlignment = xlRight
    Next c
End Sub

 

[billandrew]

Incredible response time.

Thank You sir

 

[billandrew]

Mr Rothstein

Thank You for the explanation. I did not realize this. the cell formatting is what I was looking to attain. I'll keep the other line of code in the repository.