Replace the Cell Value throughout the Entire Workbook

Question

I am using FndRplce() that code which replaces fnd = "Ilya Malikzada" with rplc = "Arham" throughout the entire workbook.

But how to create such a code which works like: When i change any name from the below attached picture that effect should apply on entire workbook.

For example "Ilya Malikzada" A2 has this first name so when i write on cell A2 Arham then wherever is "Ilya Malikzada" on entire workbook should replace with Arham.

your help will be much appreciated. Thanks

Sub FndRplce()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim ReplaceCount As Long

fnd = "Ilya Malikzada"
rplc = "Arham"

For Each sht In ActiveWorkbook.Worksheets

  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")

  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Next sht

End Sub

user_11820046 · Answer

If you want the name input to be a variable you can reference the cell specifically to do so. Just make it obvious so the user knows that's the input. I use Cell A1, but you can adjust this and the sheet name accordingly. This reference can be a excel-named reference as well as long as you keep it within it's quotes

The adjustment I made was to your rplc line like so

ActiveWorkbook.Sheets("YourSheetName").Range("A1").Value

Sub FndRplce()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim ReplaceCount As Long

fnd = "Ilya Malikzada"
rplc = ActiveWorkbook.Sheets("YourSheetName").Range("A1").Value

For Each sht In ActiveWorkbook.Worksheets

  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")

  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Next sht

End Sub

Replace the Cell Value throughout the Entire Workbook

1 Answers