Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace with Changing finds and replacements
Hello,
I want to make a number of strings uniform. These strings are all located in the same column in a worksheet. The possible replacements are all located in another worksheet of the same workbook. If and how the string is changed is dependent on the string being searched. After doing the work I want to know which replacing words were effectively used (and color the cells in which they appear) Here is an example to make things clear (the example has no relation with the problem I want to solve and is just to make things clear without bothering you with my problem): Let's say I want all English animal names that appear in a particular cell to be replaced with their French counterpart which should always be singular. e.g. worksheet 1 column A contains A1: one Dog A2: Twenty Dogs A3: One Cat A4: Twenty Cats A5: one dog And so on Worksheet 2 column A through C contain: A1: Chien (French for dog) A2: Dog A3: Dogs B1: Chat (French for Cat) B2: Cat B3: Cats B4: Catt C1: Vache (French for Cow) C2: Cow C3: Cows And so on ... So Dog, Dogs and dog should be replaced with "Chien", Cat and Cats should be replaced with "Chat". In worksheet 2 Cells A2, A3, B2 and B3 should be colored because these words were replaced while the Cells B4, C2 and C3 must not be colored because we never replaced "Cow" or "Cows" by "Vache" nor did we replace "Catt" by "Chat". My own suggestion is (without having an idea how to color the cells when a replacement is made): dim r_WS1 as integer ' The Row in worksheet 1 dim r_WS2 as integer ' The Row in worksheet 2 dim c_WS2 as integer 'The Column in Worksheet 2 dim My_replacement as range dim r_WS1 = 0 Do r_WS1 = r_WS1+1 worksheets(1).cells(1,256) = worksheets(1).cells(r_WS1,1) 'Put every string in the first cell of the last column For c_WS2 = 1 to 3 r_WS2 = 1 Do r_WS2 = r_WS2 + 1 My_replacement = worksheets(2).cells(1, c_WS2) ' The replacement is always in the first row of the respective column! Worksheets(1).Cells(1, 256).Replace _ what:=.Cells(r_WS2, cWS2), _ replacement:=My_replacement, lookat:=xlPart ' !!!!! IF A replacement is actually made, I want to color My_replacement = worksheets(2).cells(1, c_WS2) My Question is how can I check a replacement is made so I can color the cell ' !!!!! Loop Until IsEmpty(worksheets(2).Cells(r_WS2+1, c_WS2)) Next c_WS2 Loop until IsEmpty(worksheets(1).cells(r_WS1 + 2, 1) Note that as far as I know I cannot use the worksheet event : "worksheet_Change(ByVal Target As Range)" because the workbook listing the initial strings will be created out of another workbook. When I try to program the VBE from the initial workbook containing the original data to include the worksheet event in the second workbook, excel closes down for no good reason. I really hope someone can help solve my problem. Thank you, Wouter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace - problem with automatically changing formatting | Excel Discussion (Misc queries) | |||
Find and Replace - Changing 1st # only | Excel Discussion (Misc queries) | |||
Find and Replace without changing font | Excel Discussion (Misc queries) | |||
changing formulas to values so that they will be recognized by Find and Replace | Excel Discussion (Misc queries) | |||
Excel-how (find/replace) for multifmat cells w/o changing fmat | Excel Discussion (Misc queries) |