Replace the Cell Value throughout the Entire Workbook

[ShazSh]

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

I have tried with Application.InputBox("Replace with what?", "Replace Text", Type:=2) but i need more advance solution that can be like as i stated below.

For example A2 has this first name "Ilya Malikzada"  so when i write on cell "A2" "Arham" then wherever is "Ilya Malikzada" on entire workbook should replace with Arham. and same should be happened for a specific range such as "A2:A50".

Looking forward to your help thanks.

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

[#2]

@ShazSh 

Here is an approach with VBA. copy paste logic
Maybe you can adapt the code to your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
  If Target.Offset(0, 2).Value = "i.O." Then
    Tabelle1.Range("A1").Copy
    Tabelle2.Range("A" & (Tabelle2.Cells(Rows.Count, 1).End(xlUp).Row) + 1).PasteSpecial
  End If
  If Target.Offset(0, 2).Value = "N.i.O." Then
    Tabelle1.Range("A1").Copy
    Tabelle3.Range("A" & (Tabelle3.Cells(Rows.Count, 1).End(xlUp).Row) + 1).PasteSpecial
  End If
  
End If
End Sub

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

I have tried to achieved this somehow but an error is appearing "run time error: Object variable or with block variable not set" on "RngChecK = Me.range()" why it is happening i really do not know.


Sub FndRplce(fnd As String, rplc As String)

Dim sht As Worksheet
Dim boolStatus As Boolean

boolStatus = Application.ScreenUpdating
Application.ScreenUpdating = False

For Each sht In ActiveWorkbook.Worksheets

sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, LookIn:=xlValues, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Next sht
Application.ScreenUpdating = boolStatus

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim strOld As String
Dim strNew As String

rngCheck = Me.Range("A2:A37")


If Target.Count > 1 Then Exit Sub

If Intersect(Target, rngCheck) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False


strNew = Target.Value

Application.Undo
strOld = Target.Value

Call FndRplce(strOld, strNew)

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub