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", ...
excel
Macros and VBA
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