find and replace based on a column in another sheet
Posted by harold on November 21, 2001 8:56 PM
hello again! how do i make a VBA code to run find and replace function on an activesheet based on data written on another sheet. Example in sheet "find&replace", column A has all the data to find while column B has the corresponding data as replacement. The VBA has to check if the last row on column A is reached then stops executing. I want to run it say on "Sheet1". How do I do that? It's advantage is I dont have to edit the VBA code every once in a while and re-type all the new things to find and their corresponding replacement. With this set-up, all I have to do is open the "find&replace" sheet and add, edit, delete or what not. Thanks a million!!!!!
harold
Posted by Walter on November 23, 2001 12:03 AM
Sub Test()
Dim S
For Each S in WorkSheets("find&replace").Range("A1", Range("A65536").End(xlUp).Address)
Selection.Replace What:=S.Text, Replacement:=S.Offset(0,1).Text, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True
Next S
End Sub
Select all the cells that you would like to check and then run the macro.
Posted by harold on November 23, 2001 4:07 PM
hi Walter,
The code works. Thanks. However, at the moment it is only able to match entries if not mixed with other words in a phrase or sentence as opposed to the find and replace tool. how do i make it replace the word im interested with even if that word is within a sentence and the whole sentence is the content of a cell (using the same procedure of looking in a column of a specified sheet)?
harold
