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