VBA to give address of changed cells from Find and Replace

M40wen · Jan 19th 2016

Hi all,

Hopefully a quick one. What is the easiest way to give the cell references of cells that are changed following a find and replace? The code below works fine, but I want to "report" the changes onto another sheet and just need the cell address.

Code

ThisWorkbook.Sheets("Data").Activate
Rw = 2
Do Until ThisWorkbook.Sheets("FindReplace").Cells(Rw, 1) = ""
ThisWorkbook.Sheets("Data").Cells.Replace What:=ThisWorkbook.Sheets("FindReplace").Cells(Rw, 1), Replacement:=ThisWorkbook.Sheets("FindReplace").Cells(Rw, 2), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.StatusBar = "Finding rows containing '" & ThisWorkbook.Sheets("FindReplace").Cells(Rw, 1) & "' and replacing with '" & ThisWorkbook.Sheets("FindReplace").Cells(Rw, 2) & "' on Data sheet."
   Rw = Rw + 1
 Loop

**pike** · Jan 20th 2016

Re: VBA to give address of changed cells from Find and Replace

Hi
maybe use find function .. e once found replace and record the address

example only

Code

Dim Lookin As Range, ff As String
    Dim i As Long
    Dim Fnd As Variant
    Dim fCell As Range
    Dim ws As Worksheet
    Dim xItem As Variant
    
    Fnd = Array("default", "Google")


    For Each ws In Worksheets
        With Sheets(ws.Name)
            For Each xItem In Fnd
                Set Lookin = .Cells.Find(xItem, Lookin:=xlValues, LookAt:=xlPart)
                If Not Lookin Is Nothing Then
                    ff = Lookin.Address
                    Do
                     '  Lookin.Characters(InStr(1, Lookin, xItem), Len(xItem)).Font.ColorIndex = 3
                     Debug.Print Lookin.Address
                        Set Lookin = .Cells.FindNext(Lookin)
                    Loop Until ff = Lookin.Address
                End If
                Set Lookin = Nothing
            Next
        End With
    Next

Display More

M40wen · Jan 22nd 2016

Re: VBA to give address of changed cells from Find and Replace

Hi,

Thank you for this. This works well, cheers!

**pike** · Jan 22nd 2016

Re: VBA to give address of changed cells from Find and Replace

hello,
can you attach a sample file to test with?

VBA to give address of changed cells from Find and Replace

Participate now!

Share