Hi, try pasting this into a module and running. This will search in column d in each sheet other than sheet1 for each value in sheet1 column a (starting with A1) and replace with corresponding value in sheet1 column b.
It may be quite slow to run, if so you can replace Range("d:d") with something like Range("d1:d100") or what ever row number is appropriate to speed it up...
Sub FindReplace()
Dim rCell As Range
Dim sFind As String
Dim sReplace As String
Sheets("Sheet1").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
sFind = ActiveCell.Value
sReplace = ActiveCell.Offset(0, 1).Value
For Each sht In Worksheets
If sht.Name < "Sheet1" Then
For Each rCell In sht.Range("d:d").Cells
If rCell.Value = sFind Then
rCell.Value = sReplace
End If
Next rCell
End If
Next sht
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Quote:
Originally Posted by EXLNeophyte
I have a multi-page workbook. Sheet 1 column A contains a list of names, positions, addresses, etc. I would like to search Sheet 2, Sheet 3… column D for each occurrence in Sheet 1 column A. If a match is found then replace it with the contents of Sheet 1 column B same row that the matched word is found.
For Example:
Sheet 1 Column A, row 2 <dog….Sheet 2 Column D, row 10 finds <dog, then goes back to Sheet 1 Column B row 2 reads <cat and replaces <cat for <dog on Sheet 2 Column D, row 10. The find and replace macro continues until all words on Sheet 1 Column A have been searched...there are more than 750 user entries containing letters and numbers.
|