Excel VBA - 根据价值比较工作表行 [英] Excel VBA - Compare worksheet rows based off of value

问题描述

与此类似的问题：

找到2个Excel工作表之间的区别？

比较两个excel表

我的问题具体来说，我有一个每月的员工列表，具有唯一的ID和约30列其他数据，约900名员工。

My issue specifically, I have a monthly employee listing with a unique ID and around 30 columns of other data for around 900 employees.

我正在尝试完成两件事：

I'm trying to accomplish two things:

1. 在列表之间添加或删除。


2. 在每个员工的工作表之间比较该员工的其他数据更改。即职位更改。

大多数比较加载项/模块我发现只能按顺序比较特定的范围，因此一旦有差异，如果发现每个后续的行将不同

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.

首先，我想知道是否有现有的工具可以做到这一点。如果不是我在想建立自己的。我正在考虑通过循环遍历每个员工并使用vlookup来验证匹配。我担心这么多循环会使宏难以使用。关于我应该怎么做的任何指导谢谢。

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.

推荐答案

未经测试，但会给您一个从...开始的地方
这没有找到ex - 在旧表上但不在当前表上的员工。

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

这篇关于Excel VBA - 根据价值比较工作表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！