时间戳

首页 > 解决方案 > Excel上用于替换单词的宏

excel - Excel上用于替换单词的宏

问题描述

我正在尝试制作一个宏来读取我设置的某些单词（在特定的 excel 工作表中）我去一个新的 excel 工作表中一个一个地重写它们......我正在附上代码......给我这个错误我该如何解决？

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

错误

标签： excelvbavisual-studioreplacefind

解决方案

你的代码有点混乱。考虑这个简化版本。

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

有关所有详细信息，请参阅下面的链接。

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

推荐阅读