excel 批量查找和替换列单元格中的相同值

rqdpfwrv 于 2022-12-24 发布在其他

关注(0)|答案(1)|浏览(283)

我正在尝试创建一个Excel VBA函数来搜索A列的值，并在H列中找到相同的单元格，然后将B2：F6中的这些单元格替换为J2：N4的值。
我的输入文件：

预期输出：

我试过下面的VBA代码，但它不起作用。它找到并替换列Replace1的值，忽略Replace2，3，...。

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

excel

来源：https://stackoverflow.com/questions/74873956/bulk-find-and-replace-same-values-of-in-cells-of-a-column

1条答案

按热度按时间

eqqqjvef1#

看起来两个数据集都有相同的标题，所以你可以从中受益。如果标题总是相同的，并且排序相同，那么只需复制整行：

Sub test()
'if headers of both datasets are always the same and sorted the same way, just copy whole row

Dim rngDestiny As Range
Dim rngSource As Range
Dim rngFind As Range
Dim rng As Range
Dim i As Long
Dim RowN As Long
Dim LR As Long

Set rngSource = Range("I2:M4")
Set rngFind = Range("H2:H4")
Set rngDestiny = Range("B2:F6")

LR = Range("A" & Rows.Count).End(xlUp).Row 'last non-blank cell in column f-name

For i = 2 To LR Step 1
    With Application.WorksheetFunction
    'check if the value of f-name exists in column FIND
        If .CountIf(rngFind, Range("A" & i).Value) > 0 Then
            'there is a match, get row number and copy
            RowN = .Match(Range("A" & i).Value, rngFind, 0)
            rngSource.Rows(RowN).Copy rngDestiny.Rows(i - 1) 'minus 1 because our first row of data starts with i=2!!!
        End If
    End With
Next i

Set rngSource = Nothing
Set rngFind = Nothing
Set rngDestiny = Nothing

End Sub

赞(0）回复(0）举报 2022-12-24

我来回答

excel 批量查找和替换列单元格中的相同值

1条答案

相关问题

热门标签

最新问答