Forum Discussion

ShazSh's avatar
ShazSh
Brass Contributor
Jun 15, 2021

Replace the Cell Value throughout the Entire Workbook

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

 

excel
Macros and VBA

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jun 16, 2021

    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.

    • ShazSh's avatar
      ShazSh
      Brass Contributor
      Jun 16, 2021
      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

Resources