PDA

View Full Version : [SOLVED] Find and Replace macro is not working



Campbell987
03-20-2019, 07:06 PM
I created a macro to find and replace a specific data for 12 worksheets. I added a message that stated that it found and replaced the specific data, but when I checked the worksheets, the data was not replaced at all. What am I missing?

I have attached the message, and my macro is listed below. Any assistance is appreciated. Thanks!


Sub Find_Replace_Data()
'
' Replace_Data Macro
'
'
If MsgBox("Do You Want to Find & Replace the Data?", vbYesNo + vbDefaultButton2) = vbNo Then
End
End If

Dim I As Long
Dim ReplaceCount As Long


Set Cell_1 = Range("I_data_cell_1")
Set Amt_1 = Range("I_data_amt_1")
Set Cell_from_tab = Range("I_data_from_tab")
Set Cell_to_tab = Range("I_data_to_tab")

For I = Cell_from_tab To Cell_to_tab
With Sheets(I + 1)
ReplaceCount = ReplaceCount + Application.CountIf(.Cells, "*" & Cell_1 & "*")
.Cells.Replace _
What:=Cell_1, _
Replacement:=Amt_1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
Range("A1").Select

Next I

MsgBox "I have completed my search and made replacements to " & Cell_1 & " with " & Amt_1 & " in " & ReplaceCount & " cell(s)."
End Sub

大灰狼1976
03-20-2019, 08:02 PM
Hi Campbell987!
Can you add a workbook?

Campbell987
03-20-2019, 10:17 PM
Hi
,
I found the problem. I the worksheets password protected, and once I unprotected the worksheets, the macro worked.


Thank you for reply to my thread, it is very much appreciated.