Excel VBA - 根据值比较工作表行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8084097/
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 - Compare worksheet rows based off of value
提问by kwilmarth
Similar issue to these:
类似的问题：
Find the differences between 2 Excel worksheets?
My issue specifically, I have a monthly employee listing with a unique ID and around 30 columns of other data for around 900 employees.
特别是我的问题，我有一个每月员工列表，其中包含一个唯一 ID 和大约 900 名员工的大约 30 列其他数据。
I'm trying to accomplish two things:
我试图完成两件事：
- Compare if employees were added or dropped between the lists.
- Between the sheets for each employee compare what other data for that employee changed. i.e. Job Title changed.
- 比较是否在列表之间添加或删除了员工。
- 在每个员工的工作表之间比较该员工的其他数据发生了哪些变化。即职位名称已更改。
Most compare add-ins/modules I find only compare the specific ranges in order, thus once once difference if found every subsequent row will be different.
我发现大多数比较加载项/模块只按顺序比较特定范围，因此如果找到一次差异，每个后续行都会不同。
First, I'm wondering if there are any existing tools that can do this. If not I was thinking of building my own. I was thinking of doing this by looping through each employee and using vlookup to verify matches. I'm concerned doing this many loops will make the macro difficult to use. Any guidance on how I should go about this? Thanks.
首先，我想知道是否有任何现有的工具可以做到这一点。如果没有，我正在考虑建立自己的。我正在考虑通过遍历每个员工并使用 vlookup 来验证匹配来做到这一点。我担心做这么多循环会使宏难以使用。关于我应该如何去做的任何指导？谢谢。
回答by Tim Williams
Untested, but will give you a place to start from... This does not find ex-employees which are on the "old" sheet but not on the "current" sheet.
未经测试，但会给你一个起点......这不会找到在“旧”工作表上但不在“当前”工作表上的前雇员。
Sub CompareEmployeeInfo()
Const ID_COL As Integer = 1 ' ID is in the first column
Const NUM_COLS As Integer = 30 'how many columns are being compared?
Dim shtNew As Excel.Worksheet, shtOld As Excel.Worksheet
Dim rwNew As Range, rwOld As Range, f As Range
Dim x As Integer, Id
Dim valOld, valNew
Set shtNew = ActiveWorkbook.Sheets("Employees")
Set shtOld = ActiveWorkbook.Sheets("Employees")
Set rwNew = shtNew.Rows(2) 'first employee on "current" sheet
Do While rwNew.Cells(ID_COL).Value <> ""
Id = rwNew.Cells(ID_COL).Value
Set f = shtOld.UsedRange.Columns(ID_COL).Find(Id, , xlValues, xlWhole)
If Not f Is Nothing Then
Set rwOld = f.EntireRow
For x = 1 To NUM_COLS
If rwNew.Cells(x).Value <> rwOld.Cells(x).Value Then
rwNew.Cells.Interior.Color = vbYellow
Else
rwNew.Cells.Interior.ColorIndex = xlNone
End If
Next x
Else
rwNew.Cells(ID_COL).Interior.Color = vbGreen 'new employee
End If
Set rwNew = rwNew.Offset(1, 0) 'next row to compare
Loop
End Sub
回答by Jon49
Don't know if there is anything that does that for you or not. But, you can use the Dictionary Objectto make this comparison task much easier. You can also take examples from this answer that uses Dictionarieswhich checked for uniques and is optimized for speed, change it to what you need. Then you can use this fast methodto color the cells or whatever you want to do with it.
不知道是否有任何东西可以为您做到这一点。但是，您可以使用Dictionary Object来简化此比较任务。您还可以从这个答案中Dictionaries举例，使用which 检查唯一性并针对速度进行优化，将其更改为您需要的。然后，您可以使用这种快速方法为单元格着色或进行任何您想做的事情。
I know I'm not providing code for you but these pointers will get you started, and if you have more questions I can help you out.
我知道我不会为您提供代码，但这些指针将帮助您入门，如果您有更多问题，我可以帮助您。
