在VBA中尝试创建多表多准则的动态求和公式

Sub DynamicSum()
    Dim ws As Worksheet
    Dim criteriaRange As Range
    Dim sumRange As Range
    Dim criteria As Variant
    Dim sumResult As Double
    
    ' 设置工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' 设置准则范围和求和范围
    Set criteriaRange = ws.Range("A1:A10")
    Set sumRange = ws.Range("B1:B10")
    
    ' 设置准则
    criteria = Array("A", "B", "C")
    
    ' 初始化求和结果
    sumResult = 0
    
    ' 循环遍历准则范围
    For Each cell In criteriaRange
        ' 检查当前单元格的值是否符合准则
        For i = LBound(criteria) To UBound(criteria)
            If cell.Value = criteria(i) Then
                ' 如果符合准则，则将对应的求和范围的值累加到求和结果中
                sumResult = sumResult + sumRange.Cells(cell.Row, 1).Value
                Exit For
            End If
        Next i
    Next cell
    
    ' 输出求和结果
    MsgBox "动态求和结果为：" & sumResult
End Sub