跨两个工作簿循环统计：基于WS2名称统计WS1重复次数

需求说明 #

• 以WS2为参考表，表中包含不重复的名称
• 针对WS2中每个名称，统计其在WS1指定列中的重复次数
• 将统计结果写入WS2的第5列（E列）

示例数据 #

WS1（Sheet 1）C列数据 #

C1: Names
C2: Bob
C3: Tom
C4: LEE
C5: LEE
C6: Bob
C7: Sam
C8: LEE
C9: Bob
C10: Tom

WS2（Sheet 2）目标效果 #

A1: Names E1: Sum
A2: Bob E2: 3
A3: Tom E3: 2
A4: LEE E4: 3
A5: Sam E5: 1

原代码存在的问题 #

原代码有两处关键问题，导致无法正确输出统计结果：

1. 变量声明顺序错误：先定义了Rng1和Rng2，但此时LastRow1和LastRow2还未赋值，导致范围引用无效
2. 未将统计结果写入单元格：计算出count后，没有把值赋给WS2的E列对应单元格

修正后的VBA代码 #

Sub closed()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Workbooks("Close").Worksheets("Sheet 1")
    Set WS2 = Workbooks("Usernames").Worksheets("Sheet 2")
    
    Dim LastRow1 As Integer, LastRow2 As Integer
    ' 先获取最后一行行号
    LastRow1 = WS1.Cells(WS1.Rows.Count, 3).End(xlUp).Row
    LastRow2 = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row
    
    Dim Rng1 As Range
    Dim Rng2 As Range
    ' 再根据行号定义单元格范围
    Set Rng1 = WS1.Range("C2:C" & LastRow1)
    Set Rng2 = WS2.Range("A2:A" & LastRow2)
    
    Dim j As Integer, str As String
    Dim count As Integer
         
    j = 2
    Do While j <= LastRow2
        str = WS2.Cells(j, 1).Value
        ' 统计WS1中当前名称的出现次数
        count = Application.WorksheetFunction.CountIf(Rng1, str)
        ' 将统计结果写入WS2的第5列（E列）
        WS2.Cells(j, 5).Value = count
        j = j + 1
    Loop
End Sub

代码说明 #

1. 调整变量顺序：先获取LastRow1和LastRow2的行号，再基于行号定义数据范围Rng1和Rng2，确保范围引用有效
2. 简化统计函数：因为只需要单条件统计，使用CountIf即可替代CountIfs，功能完全一致且更简洁
3. 添加赋值语句：计算出次数后，将count的值直接写入WS2对应行的E列单元格，完成结果输出
4. 优化变量：删除了未使用的变量col和i，让代码更简洁

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