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

I've attached a GIF for a good understanding:


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

Recorded GIF

  • 1
    It's certainly possible to do. Although it's not 100% clear exactly what you want to achieve. An example picture and one with expected result would help for a more complete answer. Sep 7, 2020 at 13:56
  • dear Cristofer thanks for your reply i attached the pictures
    – Ayhan
    Sep 7, 2020 at 16:27
  • Dear @Cristofer , is it possible to have thist output with VBA codes ?
    – Ayhan
    Sep 9, 2020 at 15:55
  • I'm still not all that clear on what you need. If you could do sheet 2 into more of an "easy" example, and then show the desired output in a new image, because I'm not sure what you mean with "number of changes". Sep 9, 2020 at 16:01
  • aboute number of changes : quantity of changes for example how many IPs finded from C2 thats exist in sheet2 im recording my scrren and change an IP for example i will share it here 5 min later
    – Ayhan
    Sep 9, 2020 at 16:50

1 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
            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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.