VBA Find and Replace in All Workbook

[SammiBoi]

I wanted to find and replace the word AAA with the word BBB on all worksheets in my workbook.

Should I use the "Selection" arguement or should I be using something else?

<Code>
Selection.Replace What:="AAA", Replacement:="BBB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
</Code>

Thanks
Sam

 

[sous2817]

This seemed to work for me:

Cells.Replace What:="AAA", Replacement:="BBB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[SammiBoi]

This seemed to work for me:

Cells.Replace What:="AAA", Replacement:="BBB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

that seem to only work for me on the active worksheet but not on the rest of the worksheets....

 

[MrKowz]

Try:

Sub FindReplaceAll()
Dim ws      As Worksheet
For Each ws In ActiveWorkbook.Worksheets
   ws.Cells.Replace What:="AAA", Replacement:="BBB", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next ws

 

[sous2817]

That's interesting. I changed the option to search from "Worksheet" to "Workbook" and that was the generated code.

Try:

Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        .Cells.Replace What:="AAA", Replacement:="BBB", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
Next ws
End Sub

 

[analyze6]

You don't need to loop through all worksheets. I thought I had to but then realized it was something to do with the other arguments in the replace method. Cells applies to the active workbook. Here is my routine that worked for me (goes through a list of cells performing several (Replace All' iterations)..

Sub f_and_r() 'routine for making a series of workbook find+replace changes


'place cursor here and press F8 repeatedly to step through the code, open excel sheet in a seperate window to see changes being made as you execute each line


Dim i, j, k As Integer
Dim sht As Worksheet
Dim bk As Workbook
Dim what1, replace1 As String


Set bk = ThisWorkbook
Set sht = ThisWorkbook.Sheets("work")
For i = 1111 To 1124 'rows to loop through
what1 = sht.Range("a" & i) 'the string to find
replace1 = Left(Range("a" & i), 4) & "blog" & Mid(Range("a" & i), 9, 10) 'the replacement for the find string
Debug.Print what1, replace1 'just a quick reference check to make sure what and if are correct
Cells.Replace what1, replace1 'executing the replace method on the whole workbook
Next i


End Sub

Thanks,

Craig
Analyze6 - Applied Data Analysis Techniques

 

[naveeddil]

I'm having a problem in macro coding in my Dataset
When i try to find "AAA" and replace with "BBB"
it also find and replace "AAACC" with "BBBCC"

any solution is appreciated

 

[analyze6]

Hi There--in Excel you can do this using 'match entire cell contents' when doing a search and replace. I just used the macro recorder and here is the code it spits out (below). You will notice the LookAt argument = xlWhole. So just use that as an argument in the cells.replace method.. like Cells.Replace "AAA", "CCC", LookAt:=xlWhole... hopefully that should help you solve it.

Cheers,

Craig


Sub Macro1()
Cells.Replace What:="AAA", Replacement:="CCC", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

 

[naveeddil]

Thank a Million and Worked like a Charm
Can you please reply on a thread here http://www.mrexcel.com/forum/excel-questions/918225-batch-replace-excel.html