使用SUMPRODUCT实现分组排名的VBA循环代码问题排查

需求说明：实现与Excel公式=SUMPRODUCT(($A$2:$A$10000=A2)*($C$2:$C$10000>C2))+1完全一致的效果——按A列分组，对每组内C列的数值做降序排名，且代码能自动适配到数据最后一行。

原代码的问题点： #

• 变量名拼写错误：lasrowA应为lastrowA
• 范围定义错误：Range("A:A" & lastrowA)写法不符合Excel范围规则，正确范围应为Range("A2:A" & lastrowA)（从数据行第2行开始）
• Evaluate公式逻辑错误：原公式条件写错，且无法在Evaluate的字符串中直接引用VBA的Cells(i,1)，需将当前行的分组值、数值拼入公式字符串
• 未指定工作表：Cells(i,4)未绑定目标工作表，可能导致数据写入到错误表格
• 变量类型风险：i用Integer可能因行数超过32767溢出，改为Long更安全

修正后的代码： #

Sub Ranking()
    Dim lastrowA As Long
    ' 获取A列最后一行数据的行号
    lastrowA = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
    
    Dim i As Long
    Dim currentGroup As String
    Dim currentValue As Double
    
    For i = 2 To lastrowA
        currentGroup = Sheet1.Cells(i, 1).Value
        currentValue = Sheet1.Cells(i, 3).Value
        ' 动态构造SUMPRODUCT公式，适配实际数据行数
        Sheet1.Cells(i, 4).Value = Evaluate("SUMPRODUCT((Sheet1!$A$2:$A$" & lastrowA & "=""" & currentGroup & """)*(Sheet1!$C$2:$C$" & lastrowA & ">" & currentValue & ")) + 1")
    Next i
End Sub

代码说明： #

1. 用Long类型存储行号，避免大行数场景下的溢出问题
2. 循环中先提取当前行的分组名称和数值，再拼入公式字符串，确保条件判断准确
3. 公式范围动态绑定到lastrowA，不再固定为A2:A10000，适配实际数据量
4. 所有单元格操作均绑定Sheet1，避免因当前激活工作表变化导致错误

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