Excel Multiple find and replace

Question

I have C2:F2 in sheet1 they should be find from all cell of sheet2 then replace C13:F13 values in sheet1 with all cells of sheet2 that have same value

Then I need to know how much value changed in any value

Sheet2: ![Sheet2][2]

I've attached a GIF for a good understanding:

[GIF][3]

It's very good and useful for my work but I want to know can we have some modification on it?

1-in beside of T1220 cells , when don't find any number , automatically delete the IPs Can we change the code that doesn't delete the IPs when doesn't find any IP in Sheet2

2-Can we did something that doesn't change the values of selected cells after pressing the button?

3-is there any way to undo after pressing the button ? undo doesn't work after pressing the button

Sorry for many questions

I can send you the original of this Excel file as a private message but its not able to send a private message in this portal

Answer 1

So, using "Find and replace" in VBA is very simple, we use Range.Replace()

Range.Replace (What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

So, to search an entire worksheet, we can do something like:

Worksheets(1).Cells.replace What:=Range("A2"), Replacement:=Range("B2")

To replace everything in the first sheet matching the value of A2with the value of B2. However, we do not know how many values are changed.

By using the .find() method, we can find each entry, count it, and move on to the next one.

What I've done is two subs, one that finds and replaces, and one that defines what should be found and replaced.

It currently changes what is selected, and is also dependent on cells being exactly where they are in the example picture, so you might want to edit a bit.

Option Explicit
Sub run()
Dim cell As Range
    With ActiveSheet
        For Each cell In Selection ' Select what cells you want to use
            Call CountReplace(cell, cell.Offset(8), 2) 'Replace with cell 8 cells down, and on sheet number 2
        Next cell
    End With
End Sub

Sub CountReplace(ByVal find_me As Range, replace_with As Range, dataSheet As Long)
Dim c As Range, i As Long
If find_me = replace_with Then Exit Sub 'Skip if the numbers are the same
i = 0
With Worksheets(dataSheet).UsedRange
    Set c = .find(find_me, LookIn:=xlValues)
    If Not c Is Nothing Then
        Do
            c.replace What:=find_me, Replacement:=replace_with 'The find and replace function
            Set c = .FindNext(c)
            i = i + 1 'Counting how many times we find something
        Loop While Not c Is Nothing
    End If
End With
find_me = replace_with 'Replace the searched number with the replaced value
find_me.Offset(1) = i 'Store the number in the cell below the selected cell
End Sub

It does have at least one flaw. If multiple entries exist in the same cell, we will find the first one, but replace all, so they will only count as one. So if that's a problem, then you'll have to figure that one out.