find and replace
I'm sure this can be done so pls assist...
I have to worksheets. The first one has two columns: Old Label and New Label. The 2nd worksheet has several columns and i want to do a search in a specific column (column O). The search should be the text in the other sheet (Old Label) and all matches should be replaced with the New Label. Please help! |
find and replace
AFAIK this has to be done with a macro. To avoid problems with ranges I
create Range names Old_Labels, New_Labels and Labels on the sheet you want to update. Copy the code into a VB Module, ALT + F8, Insert Module and Paste the code into the module. Return to the workbook and run the code. ALT + F8, select code and click Run. Sub test() Set rng = Range("Labels") Set rngOld = Range("Old_Labels") Set rngNew = Range("New_Labels") For Each c In rng x = WorksheetFunction.Match(c, rngOld, 0) If IsError(x) Then 'do nothing Else c.Value = WorksheetFunction.Index(rngNew, WorksheetFunction.Match(c, rngOld, 0)) End If Next End Sub Regards Peter "ant1983" wrote: I'm sure this can be done so pls assist... I have to worksheets. The first one has two columns: Old Label and New Label. The 2nd worksheet has several columns and i want to do a search in a specific column (column O). The search should be the text in the other sheet (Old Label) and all matches should be replaced with the New Label. Please help! |
All times are GMT +1. The time now is 02:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com