Macro to replace "0" (Zero) with Hyphen in all Cells

[Guest]

I need a macro that will search each cell and determine if it has a zero
value in it. If it does, it will replace the zero (or in the case of my
data, 0.00), with a hyphen "-".

Here is an example:

Before

A B C D
1 Item1 $5.00 EACH 2.35
2 Item2 $0.75 EACH 0.50
3 Item3 $0.00 0.00
4 Item4 $2.50 CASE 0.00

After

A B C D
1 Item1 $5.00 EACH 2.35
2 Item2 $0.75 EACH 0.50
3 Item3 - -
4 Item4 $2.50 CASE -

As you can see, if a cell is blank and has no data in it, I don't want a
hyphen in there. Only where the cell has 0.00 in it. Thanks for the help!

 

[Norman Jones]

Hi KnightRider,

Try:

'=============>>
Public Sub Tester2()
Dim rng As Range

Set rng = Range("A1:A30") '<<==== CHANGE
rng.Replace What:="0", _
Replacement:="-", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows
End Sub
'<<=============

 

[Tom Ogilvy]

for each cell in ActiveSheet.usedrange
if not isempty(cell) then
if cell.Value = 0 then
cell.Value = "-"
end if
end if
Next

--
Regards,
Tom Ogilvy

I need a macro that will search each cell and determine if it has a zero
value in it. If it does, it will replace the zero (or in the case of my
data, 0.00), with a hyphen "-".

Here is an example:

Before

A B C D
1 Item1 $5.00 EACH 2.35
2 Item2 $0.75 EACH 0.50
3 Item3 $0.00 0.00
4 Item4 $2.50 CASE 0.00

After

A B C D
1 Item1 $5.00 EACH 2.35
2 Item2 $0.75 EACH 0.50
3 Item3 - -
4 Item4 $2.50 CASE -

As you can see, if a cell is blank and has no data in it, I don't want a
hyphen in there. Only where the cell has 0.00 in it. Thanks for the

help!

 

[Danny@Kendal]

I need a macro that will search each cell and determine if it has a zero
value in it. If it does, it will replace the zero (or in the case of my
data, 0.00), with a hyphen "-".

Have you tried using the "Accounting" cell format? Does that suit your needs
or am I missing something?