Excel vba 比较值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18419484/
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
Excel vba to compare values
提问by natmee
I have 2 excel sheets sheet A and Sheet B. In Sheet A I have a named range containing Names of employees .Column B is blank. In sheet 2 I have a named range for a list of employees and days of attendance.The employee names in both sheets are not in the same order. I need to compare the name in sheet A with sheet B and when there is a match I need to copy the attendance days and put it in the Sheet A in column B against the name. I am looking for help in VBA
我有 2 个 Excel 工作表工作表 A 和工作表 B。在工作表 AI 中有一个包含员工姓名的命名范围。B 列是空白的。在工作表 2 中，我有一个员工列表和出勤天数的命名范围。两张工作表中的员工姓名顺序不同。我需要将工作表 A 中的姓名与工作表 B 中的姓名进行比较，当匹配时，我需要复制出勤天数并将其放在 B 列的工作表 A 中与姓名相对照。我正在寻找 VBA 的帮助
Any help is appreciated
任何帮助表示赞赏
this is what I have so far
这是我迄今为止所拥有的
Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
` Dim table1
Dim table2
Dim cl
table1 = Sheet1.Range("A2:A13")
table2 = Sheet2.Range("A2:A13")
Dept_Row = Sheet1.Range("B2").Row
Dept_Clm = Sheet1.Range("B2").Column
For Each cl In table1
Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, table2, 2, False)
Dept_Row = Dept_Row + 1
Next cl
MsgBox "Done"
End Sub
采纳答案by David Zemens
Try this, untested. Use range objects instead of variants. Your row/column counter variables are unnecessary/redundant and I remove them. You need table2 to be defined as a two column range to use the VLOOKUP function and return the value from column B. Also, get rid of On Error Resume Nextthat's a bad practice to start, better to use error handling or test for error conditions and re-route.
试试这个，未经测试。使用范围对象而不是变体。您的行/列计数器变量是不必要的/冗余的，我将其删除。您需要将 table2 定义为两列范围才能使用 VLOOKUP 函数并从 B 列返回值。此外，摆脱On Error Resume Next这是一种不好的做法，最好使用错误处理或测试错误条件并重新路由.
Sub ADDCLM()
Dim table1 As Range
Dim table2 As Range
Dim cl As Range
Set table1 = Sheet1.Range("A2:A13")
Set table2 = Sheet2.Range("A2:B13") '# Modified this to 2 columns
For Each cl In table1
If Not IsError(Application.Match(cl,table2.columns(1),False) Then
cl.Offset(0,1) = Application.WorksheetFunction.VLookup(cl, table2, 2, False)
End If
Next
MsgBox "Done"
End Sub
