How to create a Macro code for find and replace and replace count in msg box

[nareshmedarmatila]

Hi Im new this forum
Excuse me with my communication errors if any

Im working with a macro which need to find irrelevant data and replace with the relevant data.

in fact i need to know how many fields are replaced?

so i need a msg box with count of replaced cells.

but i don't know the code.

Please help me with the code

Thanks

 

[sparky2205]

Hi, here's one I prepared earlier. I'm sure there are folk in the community that could do this much more elegantly but this works for me.
I designed it to only do replacements in unlocked cells as that was my requirement. So any cells you want to replace content in will have to be unlocked.
The file I was using this in was also protected by a password so removing and reapplying the password is also in the code.
This will give you the number of replacements made and the cells where the replacements were made.

Sub Find_Replace()

Dim c As Range
Dim r As Range
Dim sFind As String
Dim sReplace As String
Dim sChangedCell As String
Dim sChangedCells As String
Dim iCountCells As Integer
Dim iCountReplace As Integer
Dim iCountExist As Integer
Set r = ActiveSheet.UsedRange
Dim pw As String

Application.ScreenUpdating = False

pw = ""

iCountCells = 0
iCountReplace = 0
iCountExist = 0

'The term to be replaced
sFind = Application.InputBox("Find what?", , , , , , 2)
'If the user doesn't make an entry
If sFind = "" Or sFind = "False" Then Exit Sub

    'Check if the term being searched for exists in the document
For Each c In r
If InStr(c.Value, sFind) <> 0 Then
iCountCells = iCountCells + 1
End If
Next c

'If the term being searched for doesn't exist in the document inform the user
If iCountCells = 0 Then
MsgBox "Search item not found"
Exit Sub
    End If

'The term to replace with
sReplace = Application.InputBox("Replace with what?", , , , , , 2)
'If the user doesn't make an entry
If sReplace = "" Or sReplace = "False" Then Exit Sub

'Unprotect the worksheet
ActiveSheet.Unprotect Password:=pw

'For each cell in the used range
For Each c In r
'If it isn't locked
If c.Locked = "False" Then
'Select the cell
c.Activate
'If the cell already contains the new text
If InStr(c.Value, sReplace) <> 0 Then
iCountExist = iCountExist + 1
End If
'Replace the Find text with the new text
c.Replace sFind, sReplace, xlPart, xlByRows, False, False, False, False
'Count the number of replacements
If InStr(c.Value, sReplace) <> 0 Then
If iCountExist = 0 Then
iCountReplace = iCountReplace + 1
sChangedCell = c.Address
sChangedCells = sChangedCells & vbCrLf & sChangedCell
Else: iCountReplace = iCountReplace
End If
End If
End If
iCountExist = 0
sChangedCell = ""
Next c

Application.ScreenUpdating = True

'Inform the user of the number of replacements made
If iCountReplace = 1 Then
MsgBox iCountReplace & " replacement made at cell:" & sChangedCells
Else: MsgBox iCountReplace & " replacements made at cells:" & sChangedCells
    End If

'Reprotect the worksheet
ActiveSheet.Protect Password:=pw, _
AllowFormattingCells:="true", _
AllowFormattingRows:="true", _
AllowFormattingColumns:="true", _
AllowInsertingRows:="true", _
AllowFiltering:="true"

End Sub