How to do SUMIF with dynamic row range/variable?

0
votes

Update 7/24

Here is the code currently. 

'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

Running this gives me Run Time Error '1004' and it highlights this formula. 

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

If it helps, this is what the sheet looks like after running this

I need help with entering SUMIF formulas that have variable/dynamic rows.

I am new to VBA, so the references I've used for my code are these:
1) sum between blank rows,
2) previous question I've asked

The data that I have is separated by blank rows for every different week, which changes constantly. I'm trying to have SUMIF formulas in between each group of data, but I'm stuck on how to change it.

Reference to what my excel looks like

The first row and last row will change depending on the week. In the code, they should be defined as firstrow and lastrow, respectfully. Here are the SUMIF formulas I'm trying to put in its place.

  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))

Here is the formula I've been using as a placeholder. 

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

I've tried to simply edit the formula, but when I use the below, it gives me a syntax error. And when I put "Xingang" into a cell and use that cell as the value instead, it gives me a 1004 Error. 

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) & "))"

Here is the full code in case. 

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
2
excelvbadynamic
When using a quoted string as part of your formula you need to double-up the quotes. Eg: ActiveCell.Formula = "=""XINGANG""" Or for your example ...& "), ""XINGANG"",(" &... - Tim Williams
@Tim, I'm not sure if I've done it correctly. I've revised it to ""XINGANG"", but it now gives me Run-Time Error 1004 instead of the syntax error. - AokazuSei
@marcucciboy2, thank you for editing the format, I couldn't figure out why it turned out so weird. - AokazuSei
Your SUMIF is using the same column for both the criteria and the values to be summed. - Tim Williams
@Tim, I've tried changing the criteria column to 8 and values to be summed as J and as 12, but the Run Time Error 1004 still pops up. - AokazuSei

2 Answers

0
votes

Something more like this:

Edit - removed extra ")" in formula

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

I used this helper function to reduce the volume of your code by abstracting out the range address generation:

'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

Add this function in the module where your main code is.

0
votes

@ Tim, thank you for your help!

In case anyone has a similar question, here is what the formula looks like now. 

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