Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with find and replacing
I have the following code, but he replaces also the numbers which were
already replaced. How can I prevent that he will replace the cells which were already replaced? Maybe I can do it with a complete number or something like that? This is my code: Columns("F:F").Select Selection.Replace What:="1", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="2", Replacement:="10", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="3", Replacement:="15", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="4", Replacement:="20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="5", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="6", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="7", Replacement:="v.a. 35", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="24", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="X", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="Y", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="Z", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with find and replacing
Yes - use xlWhole instead of xlPart btw - once you set the replace parameters, they are saved and so: With Selection .Replace What:="1", Replacement:="0", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False .Replace "2", "10" .Replace "Z", "v.a. 25" End WIth Regards, Toyin. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with find and replacing
Try changing xlPart to xlWhole
-- Regards, Tom Ogilvy "bartman1980" wrote: I have the following code, but he replaces also the numbers which were already replaced. How can I prevent that he will replace the cells which were already replaced? Maybe I can do it with a complete number or something like that? This is my code: Columns("F:F").Select Selection.Replace What:="1", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="2", Replacement:="10", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="3", Replacement:="15", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="4", Replacement:="20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="5", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="6", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="7", Replacement:="v.a. 35", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="24", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="X", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="Y", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="Z", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with find and replacing
On 8 aug, 15:50, Tom Ogilvy
wrote: Try changing xlPart to xlWhole -- Regards, Tom Ogilvy "bartman1980" wrote: I have the following code, but he replaces also the numbers which were already replaced. How can I prevent that he will replace the cells which were already replaced? Maybe I can do it with a complete number or something like that? This is my code: Columns("F:F").Select Selection.Replace What:="1", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="2", Replacement:="10", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="3", Replacement:="15", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="4", Replacement:="20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="5", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="6", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="7", Replacement:="v.a. 35", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="24", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="X", Replacement:="v.a. 20", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="Y", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="Z", Replacement:="v.a. 25", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Thank you very much, it's working now! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing one text with three in IF(ISERROR(FIND.......Max | Excel Worksheet Functions | |||
Find/Replace Macro with format changes to the replacing number | Excel Programming | |||
Pasting (replacing) problem | Excel Discussion (Misc queries) | |||
Fast Find possibly replacing .xls files | Excel Programming | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions |