Macro on Excel for replace words

Question

I am trying to make a macro that reads certain words I set (in a specific excel worksheet) I go and rewrite them one by one in a new excel worksheet ...... I am attaching the code ... give me this error how do i fix it?

Sub Macro1()
'
' Macro1 Macro
'
Ricerca = "Lingua"
Campo = 5
'
    Range("A1").Select
    Cells.Find(What:=Ricerca, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
   ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Copy
    Selection.Copy
    Sheets("Foglio1").Select
    ActiveCell.Offset(1, Campo).Range("A1").Select
    ActiveSheet.Paste
For indexA = 1 To 202

    Sheets("CLIENTI per xls").Select
Cells.Find(What:=Ricerca, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Foglio1").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste


 Next indexA

 End Sub

Your code is confusing with all those `Select`s and `Activate`'s. I suggest you start by putting `Option Explicit` at the top (which will require variable declarations), and also rewrite it to get rid of the unneeded Selects and Activates. See [How to avoid using Select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Then whatever you didn't declare should become clear. Oh, and when you tell us about an error, it is also important to tell us which line produced the error. — Ron Rosenfeld, May 25 '19 at 10:07

score 0 · Answer 1 · answered Jun 04 '19 at 02:26

Your code is a bit confusing. Consider this simplified version.

Sub MultiFindNReplace()
'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

See the link below for all details.

https://www.extendoffice.com/documents/excel/1873-excel-find-and-replace-multiple-values-at-once.html

Macro on Excel for replace words

1 Answers1