Find and Replace

[pinmaster]

Hi, need help with finding and replacing data.
I found this macro on another site

Sub myReplace()
Columns("A:C").Replace What:=Range("E1").Value
Replacement:=Range("E2").Value, LookAt:=xlWhole, SearchOrder:=xlByRows
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Was wondering if anyone could adapt it for my needs. Tried to modify i
myself but don't know anything about VBA.
The data that I want to replace is in a range called "data" (A2:H500
on sheet "datainput"
Replace what ("C5") on "search" sheet
Replacement ("C17") on "search" sheet
column in this case is column 1 of "data"

TIA

Jean-Guy
Canad

 

[Alan]

Untested, but try this,

Sub myReplace()
Sheets("Data").Range("A2:H500").Replace
What:=Sheets("Search").Range("C5").Value,
Replacement:=Sheets("Search").Range("C17").Value, LookAt:=xlWhole,
SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Watch for text wrap in the mail,

Regards,

 

[pinmaster]

Hi Alan,
Tried it but it's not working, even tried the original one on a new
worksheet using the same cells from the macro, doesn't work either. Any
other ideas?

Jean-Guy
Canada