Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace "0" (Zero) with Hyphen in all Cells
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace "0" (Zero) with Hyphen in all Cells
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 '<<============= --- Regards, Norman "KnightRiderAW" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace "0" (Zero) with Hyphen in all Cells
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 "KnightRiderAW" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace "0" (Zero) with Hyphen in all Cells
"KnightRiderAW" wrote in message
... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
Macro to Replace/Delete Text Using "Watchword" List? | Excel Discussion (Misc queries) | |||
Replace dialog should put focus on "Find What" not "Replace With" | Excel Discussion (Misc queries) | |||
Macro causes "Do you want to replace the contents of the destination cells" | Excel Programming | |||
within a macro how can I suppress the warning pop "A file named xxxx.xls already exists in this location. Do you want to replace it?" | Excel Programming |