Loop sumif VBA

**hommer** · 09-25-2012, 11:11 AM

hi guys, i have the below code for sumif function

however, is there a cleaner way to loop this through to the last filled cell in column A instead of listing many lines like below? Thanks.

Sub Sumif()



    Set MyRg1 = Sheets("Data2").Range("A:A")
    Set MyRg2 = Sheets("Fruit").Range("L:L")
    Sheets("Data2").Cells(2, "B") = WorksheetFunction.SumIf(MyRg1, Cells(2, "A"), MyRg2)
    Sheets("Data2").Cells(3, "B") = WorksheetFunction.SumIf(MyRg1, Cells(3, "A"), MyRg2)
    Sheets("Data2").Cells(4, "B") = WorksheetFunction.SumIf(MyRg1, Cells(4, "A"), MyRg2)
    Sheets("Data2").Cells(5, "B") = WorksheetFunction.SumIf(MyRg1, Cells(5, "A"), MyRg2)
    Sheets("Data2").Cells(6, "B") = WorksheetFunction.SumIf(MyRg1, Cells(6, "A"), MyRg2)
    Sheets("Data2").Cells(7, "B") = WorksheetFunction.SumIf(MyRg1, Cells(7, "A"), MyRg2)
    Sheets("Data2").Cells(8, "B") = WorksheetFunction.SumIf(MyRg1, Cells(8, "A"), MyRg2)
    Sheets("Data2").Cells(9, "B") = WorksheetFunction.SumIf(MyRg1, Cells(9, "A"), MyRg2)
    Sheets("Data2").Cells(10, "B") = WorksheetFunction.SumIf(MyRg1, Cells(10, "A"), MyRg2)
    Sheets("Data2").Cells(11, "B") = WorksheetFunction.SumIf(MyRg1, Cells(11, "A"), MyRg2)
    Sheets("Data2").Cells(12, "B") = WorksheetFunction.SumIf(MyRg1, Cells(12, "A"), MyRg2)
    Sheets("Data2").Cells(13, "B") = WorksheetFunction.SumIf(MyRg1, Cells(13, "A"), MyRg2)
    Sheets("Data2").Cells(14, "B") = WorksheetFunction.SumIf(MyRg1, Cells(14, "A"), MyRg2)
    Sheets("Data2").Cells(15, "B") = WorksheetFunction.SumIf(MyRg1, Cells(15, "A"), MyRg2)
    Sheets("Data2").Cells(16, "B") = WorksheetFunction.SumIf(MyRg1, Cells(16, "A"), MyRg2)
    

End Sub

**Andy Pope** · 09-25-2012, 11:32 AM

no need for a loop.

Sub Sumif()

    Dim MyRg1 As Range
    Dim MyRg2 As Range
    Dim rngData As Range
    
    Set MyRg1 = Sheets("Data2").Range("A:A")
    Set MyRg2 = Sheets("Fruit").Range("L:L")
    
    With Sheets("Data2")
        Set rngData = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With rngData.Offset(, 1)
        .Formula = "=SUMIF(" & MyRg1.Address & ",A2,'" & MyRg2.Parent.Name & "'!" & MyRg2.Address & ")"
        .Value = .Value
    End With

End Sub

**hommer** · 09-25-2012, 12:47 PM

Hi Andy, that works perfectly but then i just notice that my sumif function only return the first number of the criteria rather than returning the sum of the total range in sheets("FRUIT").range("L:L"). can you see if my sumif is wrong?

basically my data is in sheet Fruit between column A to AD and the criteria is in sheet Data2, column A, the column im trying to sum up is sheet Fruit column L, am i missing something here? Thanks.

**Andy Pope** · 09-26-2012, 04:06 AM

You need to post example workbook. Include the actual formula you are trying to do via code so we can see expected formula and result.

**hommer** · 09-26-2012, 09:05 AM

Sure, i have amended my code and try to incorporate into the one you did but didnt work, my code below, tx

    Dim MyRg1 As Range
    Dim MyRg2 As Range


    Set MyRg1 = Sheets("FRUIT").Range("M:V")
    Set MyRg2 = Sheets("FRUIT").Range("V:V")
    Sheets("Data2").Cells(2, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(2, "A"), MyRg2)
    Sheets("Data2").Cells(3, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(3, "A"), MyRg2)
    Sheets("Data2").Cells(4, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(4, "A"), MyRg2)
    Sheets("Data2").Cells(5, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(5, "A"), MyRg2)
    Sheets("Data2").Cells(6, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(6, "A"), MyRg2)
    Sheets("Data2").Cells(7, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(7, "A"), MyRg2)
    Sheets("Data2").Cells(8, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(8, "A"), MyRg2)
    Sheets("Data2").Cells(9, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(9, "A"), MyRg2)
    Sheets("Data2").Cells(10, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(10, "A"), MyRg2)
    Sheets("Data2").Cells(11, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(11, "A"), MyRg2)
    Sheets("Data2").Cells(12, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(12, "A"), MyRg2)
    Sheets("Data2").Cells(13, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(13, "A"), MyRg2)
    Sheets("Data2").Cells(14, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(14, "A"), MyRg2)
    Sheets("Data2").Cells(15, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(15, "A"), MyRg2)
    Sheets("Data2").Cells(16, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(16, "A"), MyRg2)

**Andy Pope** · 09-26-2012, 09:45 AM

Can you not post a example workbook?

**hommer** · 09-26-2012, 10:36 AM

sure, i have amended my code below

    Dim MyRg1 As Range
    Dim MyRg2 As Range

    Set MyRg1 = Sheets("Fruit").Range("M:V")
    Set MyRg2 = Sheets("Fruit").Range("V:V")
    Sheets("Data2").Cells(2, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(2, "A"), MyRg2)
    Sheets("Data2").Cells(3, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(3, "A"), MyRg2)
    Sheets("Data2").Cells(4, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(4, "A"), MyRg2)
    Sheets("Data2").Cells(5, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(5, "A"), MyRg2)
    Sheets("Data2").Cells(6, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(6, "A"), MyRg2)
    Sheets("Data2").Cells(7, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(7, "A"), MyRg2)
    Sheets("Data2").Cells(8, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(8, "A"), MyRg2)
    Sheets("Data2").Cells(9, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(9, "A"), MyRg2)
    Sheets("Data2").Cells(10, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(10, "A"), MyRg2)
    Sheets("Data2").Cells(11, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(11, "A"), MyRg2)
    Sheets("Data2").Cells(12, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(12, "A"), MyRg2)
    Sheets("Data2").Cells(13, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(13, "A"), MyRg2)
    Sheets("Data2").Cells(14, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(14, "A"), MyRg2)
    Sheets("Data2").Cells(15, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(15, "A"), MyRg2)
    Sheets("Data2").Cells(16, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(16, "A"), MyRg2)

**Andy Pope** · 09-26-2012, 10:39 AM

how does that help with me understand what values are in myrg1 or myrg2 or what the result should be and what it actually is now?

**hommer** · 09-26-2012, 11:58 AM

with attachment

**hommer** · 09-26-2012, 11:56 AM

Apols, pls see updated code and attached file, thx

    Dim MyRg1 As Range
    Dim MyRg2 As Range

    Set MyRg1 = Sheets("Fruit").Range("A:D")
    Set MyRg2 = Sheets("Fruit").Range("D:D")
    Sheets("Data2").Cells(2, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(2, "A"), MyRg2)
    Sheets("Data2").Cells(3, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(3, "A"), MyRg2)
    Sheets("Data2").Cells(4, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(4, "A"), MyRg2)
    Sheets("Data2").Cells(5, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(5, "A"), MyRg2)
    Sheets("Data2").Cells(6, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(6, "A"), MyRg2)
    Sheets("Data2").Cells(7, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(7, "A"), MyRg2)
    Sheets("Data2").Cells(8, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(8, "A"), MyRg2)
    Sheets("Data2").Cells(9, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(9, "A"), MyRg2)
    Sheets("Data2").Cells(10, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(10, "A"), MyRg2)
    Sheets("Data2").Cells(11, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(11, "A"), MyRg2)
    Sheets("Data2").Cells(12, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(12, "A"), MyRg2)
    Sheets("Data2").Cells(13, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(13, "A"), MyRg2)
    Sheets("Data2").Cells(14, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(14, "A"), MyRg2)
    Sheets("Data2").Cells(15, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(15, "A"), MyRg2)
    Sheets("Data2").Cells(16, "B") = WorksheetFunction.SumIf(MyRg1, sheets("DATA2").Cells(16, "A"), MyRg2)

**Andy Pope** · 09-27-2012, 03:52 AM

Here is the modified code to replicate your example file.

Sub Sumif()

    Dim MyRg1 As Range
    Dim MyRg2 As Range
    Dim MyRg3 As Range
    Dim rngData As Range
    
    Set MyRg1 = Sheets("Fruit").Range("A:A")    ' Fruit Name
    Set MyRg2 = Sheets("Fruit").Range("C:C")    ' Average
    Set MyRg3 = Sheets("Fruit").Range("D:D")    ' QTY
    
    With Sheets("Data2")
        Set rngData = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With rngData
        With .Offset(, 1)
            .Formula = "=SUMIF('" & MyRg1.Parent.Name & "'!" & MyRg1.Address & ",A2,'" & MyRg2.Parent.Name & "'!" & MyRg2.Address & ")"
            .Value = .Value
        End With
        With .Offset(, 2)
            .Formula = "=SUMIF('" & MyRg1.Parent.Name & "'!" & MyRg1.Address & ",A2,'" & MyRg3.Parent.Name & "'!" & MyRg3.Address & ")"
            .Value = .Value
        End With
    End With

End Sub

Loop sumif VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Re: Loop sumif VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions