Jun 15 2021 08:55 AM - edited Jun 16 2021 01:32 AM
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
Jun 16 2021 12:02 AM
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.
Jun 16 2021 01:52 AM