vba 匹配值并将其复制到新单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1626335/
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
Match value and copy it to a new cell
提问by user196803
I'm trying to build an Excel macro that will allow me to do the following:
我正在尝试构建一个 Excel 宏，它允许我执行以下操作：
I have two Excel sheets.
我有两张 Excel 表格。
I want the macro to match all the values in Sheet1, col A against cell A1 in Sheet2. If it matches, copy cell Dx from Sheet1 to cell D1 in Sheet2. If it doesn't, just move on to cell A2 in Sheet2 and do the same, but copy cell Dx from Sheet1 to cell D2.
我希望宏将 Sheet1、col A 中的所有值与 Sheet2 中的单元格 A1 相匹配。如果匹配，将单元格 Dx 从 Sheet1 复制到 Sheet2 中的单元格 D1。如果没有，只需移至 Sheet2 中的单元格 A2 并执行相同操作，但将单元格 Dx 从 Sheet1 复制到单元格 D2。
I tried the vlookup method and I failed.
我尝试了 vlookup 方法，但失败了。
=vlookup($A1,CELLREF-SHEET1,column(),false)
=vlookup($A1,CELLREF-SHEET1,column(),false)
I'm also trying to implement this method that I've written for another project:
我也在尝试实现我为另一个项目编写的这个方法：
Dim cel As Range, celFound As Range, rg As Range, rgLookHere As Range
Dim i As Long
Dim v As Variant
Set rg = Range("A1") 'First cell to check
Set rgLookHere = rg.Offset(0, 1).EntireColumn 'Check for matches in this column
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
On Error Resume Next
ReDim v(1 To rg.Cells.Count, 1 To 1)
For Each cel In rg.Cells
i = i + 1
If cel <> "" Then
Set celFound = Nothing
Set celFound = rgLookHere.Find(cel.Value, LookIn:=xlValues, LookAt:=xlWhole)
v(i, 1) = IIf(celFound Is Nothing, "Null", "yes")
End If
Next
rg.Offset(0, 2).Value = v
On Error GoTo 0
Do you have any advice?
你有什么建议吗？
回答by Neil Kimber
VLookup seems to be the way to go. If I have data in Column A and D on Sheet1, and the data to match to in Column A in Sheet2, then I put the following VLookup in Sheet2 Column D:
VLookup 似乎是要走的路。如果我在 Sheet1 的 A 列和 D 列中有数据，并且在 Sheet2 的 A 列中有要匹配的数据，那么我将以下 VLookup 放在 Sheet2 D 列中：
=VLOOKUP(A1,Sheet1!A:D,4,FALSE)
=VLOOKUP(A1,Sheet1!A:D,4,FALSE)
You will see N/A in cells where there is no match. You can always use ISNA() to identify these and based upon an IF() you could output the actual match or nothing.
您将在没有匹配项的单元格中看到 N/A。您始终可以使用 ISNA() 来识别这些，并根据 IF() 您可以输出实际匹配或不输出。
