Replace the Cell Value throughout the Entire Workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-2449301%22%20slang%3D%22en-US%22%3EReplace%20the%20Cell%20Value%20throughout%20the%20Entire%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2449301%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EFndRplce()%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ecode%20which%20replaces%3CSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Efnd%20%3D%20%22Ilya%20Malikzada%22%26nbsp%3B%3C%2FSTRONG%3Ewith%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Erplc%20%3D%20%22Arham%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ethroughout%20the%20entire%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20with%26nbsp%3B%3CSTRONG%3EApplication.InputBox(%22Replace%20with%20what%3F%22%2C%20%22Replace%20Text%22%2C%20Type%3A%3D2)%3C%2FSTRONG%3E%20but%20i%20need%20more%20advance%20solution%20that%20can%20be%20like%20as%20i%20stated%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFor%20example%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EA2%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ehas%20this%20first%20name%26nbsp%3B%3CSTRONG%3E%22Ilya%20Malikzada%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%20so%20when%20i%20write%20on%20%3CSTRONG%3Ecell%20%22A2%22%20%22Arham%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ethen%20wherever%20is%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22Ilya%20Malikzada%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eon%20entire%20workbook%20should%20replace%20with%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EArham%3C%2FSTRONG%3E.%20and%20same%20for%20all%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20forward%20to%20your%20help%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2449301%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2452326%22%20slang%3D%22de-DE%22%3ESubject%3A%20Replace%20the%20Cell%20Value%20throughout%20the%20Entire%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2452326%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F945050%22%20target%3D%22_blank%22%3E%40Startednow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20approach%20with%20VBA.%20copy%20paste%20logic%20%3CBR%20%2F%3E%20Maybe%20you%20can%20adapt%20the%20code%20to%20your%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0AIf%20Target.Address%20%3D%20%22%24A%241%22%20Then%0A%20%20If%20Target.Offset(0%2C%202).Value%20%3D%20%22i.O.%22%20Then%0A%20%20%20%20Tabelle1.Range(%22A1%22).Copy%0A%20%20%20%20Tabelle2.Range(%22A%22%20%26amp%3B%20(Tabelle2.Cells(Rows.Count%2C%201).End(xlUp).Row)%20%2B%201).PasteSpecial%0A%20%20End%20If%0A%20%20If%20Target.Offset(0%2C%202).Value%20%3D%20%22N.i.O.%22%20Then%0A%20%20%20%20Tabelle1.Range(%22A1%22).Copy%0A%20%20%20%20Tabelle3.Range(%22A%22%20%26amp%3B%20(Tabelle3.Cells(Rows.Count%2C%201).End(xlUp).Row)%20%2B%201).PasteSpecial%0A%20%20End%20If%0A%20%20%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2452609%22%20slang%3D%22en-US%22%3EBetreff%3A%20Replace%20the%20Cell%20Value%20throughout%20the%20Entire%20Workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2452609%22%20slang%3D%22en-US%22%3EI%20have%20tried%20to%20achieved%20this%20somehow%20but%20an%20error%20is%20appearing%20%22run%20time%20error%3A%20Object%20variable%20or%20with%20block%20variable%20not%20set%22%20on%20%22RngChecK%20%3D%20Me.range()%22%20why%20it%20is%20happening%20i%20really%20do%20not%20know.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20FndRplce(fnd%20As%20String%2C%20rplc%20As%20String)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20sht%20As%20Worksheet%3CBR%20%2F%3EDim%20boolStatus%20As%20Boolean%3CBR%20%2F%3E%3CBR%20%2F%3EboolStatus%20%3D%20Application.ScreenUpdating%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20sht%20In%20ActiveWorkbook.Worksheets%3CBR%20%2F%3E%3CBR%20%2F%3Esht.Cells.Replace%20what%3A%3Dfnd%2C%20Replacement%3A%3Drplc%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20LookIn%3A%3DxlValues%2C%20MatchCase%3A%3DFalse%2C%20_%3CBR%20%2F%3ESearchFormat%3A%3DFalse%2C%20ReplaceFormat%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20sht%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20boolStatus%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20rngCheck%20As%20Range%3CBR%20%2F%3EDim%20strOld%20As%20String%3CBR%20%2F%3EDim%20strNew%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3ErngCheck%20%3D%20Me.Range(%22A2%3AA37%22)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Target.Count%20%26gt%3B%201%20Then%20Exit%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Intersect(Target%2C%20rngCheck)%20Is%20Nothing%20Then%20Exit%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EstrNew%20%3D%20Target.Value%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Undo%3CBR%20%2F%3EstrOld%20%3D%20Target.Value%3CBR%20%2F%3E%3CBR%20%2F%3ECall%20FndRplce(strOld%2C%20strNew)%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Replies

@Startednow 

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.

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