时间戳

首页 > 解决方案 > 粘贴后自动替换列中的文本

excel - 粘贴后自动替换列中的文本

问题描述

当我运行宏时，我的下面的代码正在正确替换值。但是，我希望这个宏在我将值或字符串粘贴到单元格后不久运行......意味着当我粘贴一个值时，它应该自动更改/替换值，而无需按下宏上的运行按钮......对于例如，当我将数据粘贴到 sheet1 FHH、FGA 中时，它应该自动将其替换为“FST”、“FPT”......就像粘贴后自动替换一样。

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array("FHH", "FGA")
rplcList = Array("FST", "FPT")

'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.replace What:=fndList(x), Replacement:=rplcList(x), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  
  Next x

End Sub

粘贴后，以下内容应自动替换第 2 列中的值

标签： excelvba

解决方案

我会将这段代码放在 Thisworkbook, Workbook_Sheetchange 事件中。它的优点是您不必放入所有工作表，也不必遍历所有工作表。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim fndList As Variant
    Dim rplcList As Variant
    Dim x As Long
    fndList = Array("FHH", "FGA")
    rplcList = Array("FST", "FPT")
    'Loop through each item in Array lists
    For x = LBound(fndList) To UBound(fndList)
          Target.Replace What:=fndList(x), Replacement:=rplcList(x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
    Next x
End Sub

推荐阅读