如何处理动态行范围/变量的SUMIF？

Question

更新7/24

这是当前的代码。

'insert blank row based on if Total Sum is on Column K
    Dim FirstRow As Long, LastRow As Long, Col As Long
    FinalRow = Cells(Worksheets("page1").Rows.Count, 1).End(xlUp).Row
    For j = 12 To 14
        For i = FinalRow + 8 To 1 Step -1
            Do While IsEmpty(Cells(i, j))
                If IsEmpty(Cells(i - 1, j)) Then
                    FirstRow = i - 1
                    LastRow = FirstRow
                Else
                    LastRow = i - 1
                    FirstRow = Cells(i - 1, j).End(xlUp).Row
                End If
                Cells(LastRow + 1, j) = Application.WorksheetFunction.Sum(Range(Cells(FirstRow, j), Cells(LastRow, j)))
                If Cells(LastRow + 1, 12) >= 1 Then
                    Cells(LastRow + 1, 11).FormulaLocal = "Total Sum"
                    If Cells(LastRow + 1, 11) = "Total Sum" Then
                        Cells(LastRow + 1, j) = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                    End If
                    Cells(LastRow + 3, 11).FormulaLocal = "China"
                    If Cells(LastRow + 3, 11) = "China" Then
                        Cells(LastRow + 3, j).FormulaLocal = _
                            "=Sum(SUMIF(" & Addr(FirstRow, LastRow, 8) & "), ""XINGANG""," & Addr(FirstRow, LastRow, 12) & "))"
                        'Cells(LastRow + 3, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                        'Cells(lastRow + 3, j).FormulaLocal = "=Sum(Sumif((" & Range(Cells(firstRow, 8), Cells(lastRow, 8)).Address(False, False) & "), ""Xingang"",(" & Range(Cells(firstRow, 12), Cells(lastRow, 12)).Address(False, False) & "))"
                        If Cells(LastRow + 2, 11) = "" Then
                            Worksheets("Page1").Cells(LastRow + 2, j).ClearContents
                        End If
                    End If
                    Cells(LastRow + 4, 11).FormulaLocal = "Abu Dhabi"
                    If Cells(LastRow + 4, 11) = "Abu Dhabi" Then
                        Cells(LastRow + 4, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                    End If
                    Cells(LastRow + 5, 11).FormulaLocal = "Other"
                    If Cells(LastRow + 5, 11) = "Other" Then
                        Cells(LastRow + 5, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                    End If
                    Cells(LastRow + 6, 11).FormulaLocal = "H1 & H2"
                    If Cells(LastRow + 6, 11) = "H1 & H2" Then
                        Cells(LastRow + 6, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                    End If
                    Cells(LastRow + 7, 11).FormulaLocal = "Product"
                    If Cells(LastRow + 7, 11) = "Product" Then
                        Cells(LastRow + 7, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                        If Cells(LastRow + 7, 12) >= 1 Then
                            For Z = 11 To 14         '
                                Cells(LastRow + 12, 11).EntireRow.ClearContents
                                Cells(LastRow + 13, 11).EntireRow.ClearContents
                                Cells(LastRow + 14, 11).EntireRow.ClearContents
                                Cells(LastRow + 8, 11).FormulaLocal = "Delete"
                                Cells(LastRow + 9, 11).FormulaLocal = "Delete"
                                Cells(LastRow + 10, 11).FormulaLocal = "Delete"
                                Cells(LastRow + 11, 11).FormulaLocal = "Delete"
                            Next Z
                        End If
                    End If
                End If
            Loop
        Next i
    Next j



'Finding 'Delete' and delete entire row
    Dim rFound As Range, Str As String
    On Error Resume Next

    Str = "Delete"
    Do
        Set rFound = Cells.Find(Str, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows)
        If Not rFound Is Nothing Then Rows(rFound.Row).EntireRow.Delete xlShiftUp
    Loop Until rFound Is Nothing


    Application.ScreenUpdating = True

End Sub

Function Addr(FirstRow As Long, LastRow As Long, Col As Long) As String
    Addr = Range(Cells(FirstRow, Col), Cells(LastRow, Col)).Address(False, False)
End Function

运行这给我运行时错误'1004‘，它突出了这个公式。

Cells(LastRow + 3, j).FormulaLocal = _
                                "=Sum(SUMIF(" & Addr(FirstRow, LastRow, 8) & "), ""XINGANG""," & Addr(FirstRow, LastRow, 12) & "))"

如果有帮助的话，运行以下操作后的工作表是这样的

我需要帮助输入带有变量/动态行的SUMIF公式。

我是VBA的新手，所以我在代码中使用的引用如下：

1) 空白行和，

2) 我之前问过的问题

我所拥有的数据每周都由空白行分隔，这种情况会不断变化。我试图在每一组数据之间加入SUMIF公式，但我仍然无法改变它。

参考我的excel是什么样子

第一行和最后一行将根据周的不同而改变。在代码中，应该将它们定义为第一行和最后一行。这是我试图用的SUMIF公式。

1. =SUMIF(P138:P158,"<>* Hold *",L138:L158)
2. =SUM(SUMIF(H5:H21,{"Chongqing","Dalian","Fuzhou","Huangpu","Lianyungang","Nanjing","Nansha","Nantong","NingBo","Qingdao","Shekou","Xiamen","Yantian","Xingang","Shanghai","Mawei"},L5:L21))
3. =SUM(SUMIF(H5:H21,{"Abu Dhabi","Jebel","Khalifa"},L5:L21))
4. =SUM(SUMIF(O12:O28,{"*H1*"},L12:L28))
5. =SUM(SUMIF(O12:O28,{"H2","H2-PRESSED"},L12:L28))

下面是我一直使用的占位符公式。

Cells(LastRow + 7, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"

我尝试简单地编辑公式，但是当我使用下面的内容时，它会给我一个语法错误。当我把"Xingang“放入一个单元格中，并使用该单元格作为值时，它会给我一个1004错误。

Cells(LastRow + 3, j).FormulaLocal = "=Sum(SUMIF((" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & "), "XINGANG",(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & "))"

这是完整的代码以防万一。

finalrow = Cells(Worksheets("page1").Rows.Count, 1).End(xlUp).Row
For j = 12 To 14
    For i = finalrow + 8 To 1 Step -1
        Do While IsEmpty(Cells(i, j))
            If IsEmpty(Cells(i - 1, j)) Then
                FirstRow = i - 1
                LastRow = FirstRow
            Else
                LastRow = i - 1
                FirstRow = Cells(i - 1, j).End(xlUp).Row
            End If
            Cells(LastRow + 1, j) = Application.WorksheetFunction.Sum(Range(Cells(FirstRow, j), Cells(LastRow, j)))
            If Cells(LastRow + 1, 12) >= 1 Then
                Cells(LastRow + 1, 11).FormulaLocal = "Total Sum"
                If Cells(LastRow + 1, 11) = "Total Sum" Then
                    Cells(LastRow + 1, j) = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                End If
                Cells(LastRow + 3, 11).FormulaLocal = "China"
                If Cells(LastRow + 3, 11) = "China" Then
                    Cells(LastRow + 3, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                    If Cells(LastRow + 2, 11) = "" Then
                        Worksheets("Page1").Cells(LastRow + 2, j).ClearContents
                    End If
                End If
                Cells(LastRow + 4, 11).FormulaLocal = "Abu Dhabi"
                If Cells(LastRow + 4, 11) = "Abu Dhabi" Then
                    Cells(LastRow + 4, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                End If
                Cells(LastRow + 5, 11).FormulaLocal = "Other"
                If Cells(LastRow + 5, 11) = "Other" Then
                    Cells(LastRow + 5, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                End If
                Cells(LastRow + 6, 11).FormulaLocal = "H1 & H2"
                If Cells(LastRow + 6, 11) = "H1 & H2" Then
                    Cells(LastRow + 6, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                End If
                Cells(LastRow + 7, 11).FormulaLocal = "Product"
                If Cells(LastRow + 7, 11) = "Product" Then
                    Cells(LastRow + 7, j).FormulaLocal = "=Sum(" & Range(Cells(FirstRow, j), Cells(LastRow, j)).Address(False, False) & ")"
                    If Cells(LastRow + 7, 12) >= 1 Then
                        For Z = 11 To 14         '
                            Cells(LastRow + 12, 11).EntireRow.ClearContents
                            Cells(LastRow + 13, 11).EntireRow.ClearContents
                            Cells(LastRow + 14, 11).EntireRow.ClearContents
                            Cells(LastRow + 8, 11).FormulaLocal = "Delete"
                            Cells(LastRow + 9, 11).FormulaLocal = "Delete"
                            Cells(LastRow + 10, 11).FormulaLocal = "Delete"
                            Cells(LastRow + 11, 11).FormulaLocal = "Delete"
                        Next Z
                    End If
                End If
            End If
        Loop
    Next i
Next j

Answer 1

更像是这样的：

编辑-删除公式中的")“

Cells(LastRow + 3, j).FormulaArray = _
      "=Sum(SUMIF(" & Addr(FirstRow, LastRow, 8) & _
      ", ""XINGANG""," & Addr(FirstRow, LastRow, j) & "))"

通过抽象出范围地址生成，我使用了这个助手函数来减少代码的数量：

'helper function
Function Addr(firstRow as Long, lastRow as long, col as long) As String
    Addr = Range(Cells(firstRow, col), Cells(lastRow, col)).Address(False, False)
End Function

将此函数添加到主代码所在的模块中。