VBA银行对账单对账：同名多实例薪资匹配需求求助

Hey there! As someone who’s been in your shoes as a VBA newbie, I know how frustrating it can be when you hit a wall after getting the first part of your project working. Let’s put together a straightforward macro that checks all your boxes—no fancy jargon, just functional code you can tweak to fit your workbook.

First, Let’s Lock In the Requirements #

Just to make sure we’re aligned, here’s what we’re building:

• Pull the first name only from column D of your left "语句表" (statement sheet)
• Match this name to column G of your right "主文件" (master sheet), filtered by the corresponding period (1-9 for months/statements)
• Compare the salary from statement column E to master column AV:
  • If the name is unique (only one match) AND salaries match → mark column X with "a"
  • If there are multiple name matches, but this salary is unique among them → mark column X with "a"
  • If 2+ people with the same name share this exact salary → mark column Y with the count (e.g., "2") or "b" (I’ll include both options so you can pick)

The VBA Code #

Paste this into a new module in your workbook (press Alt+F11 to open the VBA editor, right-click your workbook in the Project pane, select Insert > Module):

Sub ReconcileSalaries()
    ' Define worksheet objects - UPDATE THESE NAMES TO MATCH YOUR WORKBOOK!
    Dim wsStmt As Worksheet, wsMaster As Worksheet
    Set wsStmt = ThisWorkbook.Sheets("语句表") ' Left statement sheet
    Set wsMaster = ThisWorkbook.Sheets("主文件") ' Right master sheet
    
    ' Variables for loop and data tracking
    Dim i As Long, j As Long, lastStmtRow As Long, lastMasterRow As Long
    Dim empName As String, stmtPeriod As Integer, stmtSalary As Double, masterSalary As Double
    Dim salaryCount As Integer, nameMatchCount As Integer
    Dim salaryDict As Object ' Tracks how many times each salary appears for the name/period
    
    ' Get the last row with data in each sheet (avoids looping empty rows)
    lastStmtRow = wsStmt.Cells(wsStmt.Rows.Count, "D").End(xlUp).Row
    lastMasterRow = wsMaster.Cells(wsMaster.Rows.Count, "G").End(xlUp).Row
    
    ' Initialize the dictionary for salary counting
    Set salaryDict = CreateObject("Scripting.Dictionary")
    
    ' Loop through each row in the statement sheet (start at row 2 to skip headers)
    For i = 2 To lastStmtRow
        ' Reset the dictionary for each new name/period
        salaryDict.RemoveAll
        
        ' Pull data from current statement row - ADJUST PERIOD COLUMN IF NEEDED!
        empName = wsStmt.Cells(i, "D").Value ' Name from column D
        stmtPeriod = wsStmt.Cells(i, "A").Value ' Period from column A (change if your period is elsewhere)
        stmtSalary = wsStmt.Cells(i, "E").Value ' Salary from column E
        
        ' Count total name+period matches in the master sheet
        nameMatchCount = Application.WorksheetFunction.CountIfs( _
            wsMaster.Range("G:G"), empName, _
            wsMaster.Range("A:A"), stmtPeriod) ' Update "A:A" to your master sheet's period column
        
        ' Collect all salaries for this name+period to count duplicates
        For j = 2 To lastMasterRow
            If wsMaster.Cells(j, "G").Value = empName And wsMaster.Cells(j, "A").Value = stmtPeriod Then
                masterSalary = wsMaster.Cells(j, "AV").Value
                ' Add salary to dictionary (track occurrence count)
                If salaryDict.Exists(masterSalary) Then
                    salaryDict(masterSalary) = salaryDict(masterSalary) + 1
                Else
                    salaryDict(masterSalary) = 1
                End If
            End If
        Next j
        
        ' Check if the statement salary exists in the master data
        If salaryDict.Exists(stmtSalary) Then
            salaryCount = salaryDict(stmtSalary)
            
            ' Apply your labeling rules
            If nameMatchCount = 1 Or salaryCount = 1 Then
                ' Unique name OR unique salary among same names - mark X with "a"
                wsStmt.Cells(i, "X").Value = "a"
                wsStmt.Cells(i, "Y").ClearContents
            Else
                ' 2+ people share this name+salary - mark Y with count or "b"
                wsStmt.Cells(i, "Y").Value = salaryCount ' Use this for the exact number (e.g., "2")
                ' wsStmt.Cells(i, "Y").Value = "b" ' Uncomment this if you prefer "b" instead of the count
                wsStmt.Cells(i, "X").ClearContents
            End If
        Else
            ' No matching salary found (optional: customize this message)
            wsStmt.Cells(i, "X").Value = "No salary match"
            wsStmt.Cells(i, "Y").ClearContents
        End If
    Next i
    
    ' Clean up objects to free memory
    Set salaryDict = Nothing
    Set wsStmt = Nothing
    Set wsMaster = Nothing
    
    MsgBox "Reconciliation done!", vbInformation
End Sub

Key Adjustments You Need to Make #

1. Worksheet Names: Change "语句表" and "主文件" to the actual names of your sheets.
2. Period Columns: The code assumes the period (1-9) is in column A of both sheets. If your period is in another column (e.g., column B), update the wsStmt.Cells(i, "A").Value and wsMaster.Range("A:A") parts.
3. Labeling Preference: Choose whether you want column Y to show the exact count or "b" by commenting/uncommenting the relevant line in the code.

How to Use It #

1. Save your workbook as a .xlsm file (required for macros).
2. Open the VBA editor (Alt+F11), insert a module, and paste the code.
3. Tweak the parts mentioned above to fit your workbook setup.
4. Run the macro (press F5 in the editor, or go to Developer > Macros > select ReconcileSalaries > Run).

This code is simple to follow and does exactly what you need—no overcomplicated optimizations, just reliable functionality. If you hit any snags adjusting it, feel free to tweak the parts that don’t fit your workbook!

内容的提问来源于stack exchange，提问作者kltk7