vba 比较两列，如果匹配将其替换为旁边的另一个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20998802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare two columns, if a match replace one with another cell beside
提问by Treckez
I am trying to add category codes to text categories in an excel spreadsheet. I have a column A with all the category names in text, including duplicates. I have column B with all the category names in text, not including duplicates and I have column C which has the category codes not including duplicates. Each code in column C alligns with its category in column B For example:
我正在尝试将类别代码添加到 Excel 电子表格中的文本类别。我有一个 A 列，其中包含文本中的所有类别名称，包括重复项。我有 B 列，其中包含文本中的所有类别名称，不包括重复项，而 C 列的类别代码不包括重复项。C 列中的每个代码都与 B 列中的类别对齐 例如：
A:
Domestic WareB:
Agri ToolsC:
051
A:
国内商品B:
农具丙：
051
051 is the code for agri tools. I want to take the string in column A, Search for this string in column B and then replace the string that was in column A with the code in C.
051是农具的代号。我想取 A 列中的字符串，在 B 列中搜索此字符串，然后用 C 中的代码替换 A 列中的字符串。
Is there anyway to do this using VBA or excels built in functions?
无论如何使用VBA或excels内置函数来做到这一点？
采纳答案by MikeD
If column A contains a list of Category names including duplicates, and columns B & C contain (the same) category names and Category ID's without duplicates (i.e. a "category code table"), you can
如果 A 列包含包含重复项的类别名称列表，并且 B 列和 C 列包含（相同）类别名称和类别 ID 没有重复项（即“类别代码表”），则您可以
in column D (or maybe better insert a new column between A and B)
for each entry in A you enter
=VLOOKUP(Ax,$B$y:$C$z,2,FALSE)
在 D 列中（或者更好地在 A 和 B 之间插入一个新列）
对于您输入的 A 中的每个条目
=VLOOKUP(Ax,$B$y:$C$z,2,FALSE)
whereby
由此
x = current row number
x = 当前行号
y = starting row of "category code table"
y =“类别代码表”的起始行
z = end row of "category code table"
z =“类别代码表”的结束行
You make the coord's of the lookup table absolute to prevent y & z being changed as you copy the formula down.
您将查找表的坐标设为绝对坐标，以防止在向下复制公式时更改 y & z。
回答by Gary's Student
Since you want to replacevalues in column A, give this small macro a try:
由于您想替换A列中的值，请尝试使用这个小宏：
Sub FixColumnA()
Dim nA As Long, nB As Long, v As Variant
Dim i As Long, j As Long
nA = Cells(Rows.Count, "A").End(xlUp).Row
nB = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To nA
With Cells(i, "A")
v = .Value
For j = 1 To nB
If v = Cells(j, "B").Value Then
.Value = Cells(j, "C").Value
End If
Next j
End With
Next i
End Sub
