VBA Excel查找和替换没有替换已替换的项目 [英] VBA Excel find and replace WITHOUT replacing items already replaced

问题描述

我正在寻找一个可以找到和替换数据的excel脚本，但是对于所有的爱，我无法弄清楚如何写。

I am looking to make an excel script that can find and replace data, but for the love of everything I cannot figure out how to write it.

情况：

A ----------- B ----------- C

A-----------B-----------C

猫-------狗------香蕉

Cat-------Dog------Banana

狗------鱼------苹果

Dog------Fish------Apple

鱼------猫-------橙色

Fish------Cat-------Orange

因此，宏将查看列B中单元格中的数据，然后查看列C中的相邻单元格，并替换列A中该数据的所有实例，如果所以结果将是：

So the macro would look at the data in a cell in column B, then look at the adjacent cell in column C, and replace all instances of that data in column A with what if found in C. So the results would be:

A --------------- B- ---------- C

A---------------B-----------C

橙色------狗------香蕉

Orange------Dog------Banana

香蕉------鱼------苹果

Banana------Fish------Apple

苹果--------猫---- ---橙色

Apple--------Cat-------Orange

但是这不是全部，我希望它不会更改已经更改过一次的A中的单元格！ （我正在尝试改变背景颜色）

But that's not all, I would like it to not change cells in A that already have been changed once! (I'm trying this with changing the background colour)

任何帮助？我完全失去了。

Any help? I am at a complete loss.

编辑：

好的，我发现如何做简单的部分替换），但是我不知道如何不改变已经被改变一次的单元格。这是我的代码：

Okay I found out how to do the easy part (replacing), but I cannot find out how to not change cells that already have been changed once. Here is my code:

Sub multiFindNReplace()
    Dim myList, myRange
    Set myList = Sheets("sheet1").Range("A2:B3") 'two column range where find/replace pairs are
    Set myRange = Sheets("sheet1").Range("D2:D5") 'range to be searched
    For Each cel In myList.Columns(1).Cells
    myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value, ReplaceFormat:=True
    Next cel
End Sub

据我所知，ReplaceFormat：= True
不执行任何操作，所以已经被替换的项目一直被替换！有没有办法使这项工作？

As far as I can tell, ReplaceFormat:=True doesn't do anything ;/ so items that already have been replaced once still are being replaced! Is there a way to somehow make this work?

推荐答案

这是使用您的推荐与彩色作为一次限制的答案：

Here's the answer using your recommendation with color as a one-time limiter:

Sub Replace_Once()

'Find last row using last cell in Column B
LastRow = Range("B" & Rows.Count).End(xlUp).Row

'Clear colors in Column A
Range("A1:A" & LastRow).Interior.ColorIndex = xlNone

'Look at each cell in Column B one at a time (Cel is a variable)
For Each Cel In Range("B1:B" & LastRow)
    'Compare the cell in Column B with the Value in Column A one at a time (C is a variable)
    For Each C In Range("A1:A" & LastRow)
        'Check if the Cell in Column A matches the Cell in Column B and sees if the color has changed.
        If C.Value = Cel.Value And C.Interior.Color <> RGB(200, 200, 200) Then
            'Colors the cell
            C.Interior.Color = RGB(200, 200, 200)
            'Updates the value in Column A with the cell to the right of the Cell in Column B
            C.Value = Cel.Offset(0, 1).Value
        End If
    Next
Next

'Uncomment the line below to remove color again
'Range("A1:A" & LastRow).Interior.ColorIndex = xlNone

End Sub

这篇关于VBA Excel查找和替换没有替换已替换的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！