Search and replace vb code

[alokie]

Help... I am trying to create a button that will search for the item in field Updates!a4 and replace it with the text in Updates!c4... I need this to search through the entire workbook.. any help would be great.. I am about a green as it comes with this stuff..

Sub replace()
What = Updates!a4
repl = Updates!c4
Sheets().Select

Selection.replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Error - Object required (Error 424)

 

[shg]

One way:

Sub Replace()
    Dim wks         As Worksheet
    Dim vWhat       As Variant
    Dim vRepl       As Variant
 
    vWhat = Worksheets("Updates").Range("A4").Value
    vRepl = Worksheets("Updates").Range("C4").Value
 
    For Each wks In Worksheets
        wks.Cells.Replace What:=vWhat, _
                          Replacement:=vRepl, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          MatchCase:=False, _
                          SearchFormat:=False, _
                          ReplaceFormat:=False
    Next wks
End Sub

 

[alokie]

Thank you!!!!!! Thank you!!!!!!

 

[shg]

You're welcome.

You should also include the LookIn argument (xlValues or xlFormulas)

 

[alokie]

If I were to only need to search a specific area in a different sheet. Would I just add...

With Range("A1:A10")
Set LastCell = .Cells(.Cells.Count)
End With