Sumifs results to be stored into array and then pasted into existing table

Viewed 31

I have a table with data that looks like this:

Table1

And I need to get those results into another table (on a different tab) that looks like this:

Table 2

So I am currently using a basic Sumifs Function via VBA but it is not very efficient to write the same code for ALL columns and just changing the last Criteria (Criteria6)...

    '-------------
    '   RETAIL
    '-------------
    
    'assign the range of cells
    lr2 = WS5.Range("A" & Rows.Count).End(xlUp).row
    Set rngSum1 = WS5.Range("H2:H" & lr2)
    Set rngCriteria5 = WS5.Range("A2:A" & lr2)
    'Criteria5 = WS4.Cells(n, 10)
    Set rngCriteria6 = WS5.Range("D2:D" & lr2)
    'Criteria6 = WS4.Cells("T3")
    
    
    'use the ranges in the formula
    Dim m As Long
    Dim lr3 As Integer
    lr3 = WS4.Range("J" & Rows.Count).End(xlUp).row
    
    For m = 4 To lr3
        WS4.Cells(m, 20).Value = Application.WorksheetFunction.SumIfs(rngSum1, rngCriteria5, WS4.Cells(m, 10), rngCriteria6, WS4.Range("T3"))
    Next m
    
    'release the range object
    Set rngCriteria5 = Nothing
    Set rngCriteria6 = Nothing

    '-------------
    '   INDUSTRY
    '-------------
    
    'assign the range of cells
    lr2 = WS5.Range("A" & Rows.Count).End(xlUp).row
    Set rngSum1 = WS5.Range("H2:H" & lr2)
    Set rngCriteria5 = WS5.Range("A2:A" & lr2)
    'Criteria5 = WS4.Cells(n, 10)
    Set rngCriteria6 = WS5.Range("D2:D" & lr2)
    'Criteria6 = WS4.Cells("U3")
    
    
    'use the ranges in the formula
    Dim m As Long
    Dim lr3 As Integer
    lr3 = WS4.Range("J" & Rows.Count).End(xlUp).row
    
    For m = 4 To lr3
        WS4.Cells(m, 21).Value = Application.WorksheetFunction.SumIfs(rngSum1, rngCriteria5, WS4.Cells(m, 10), rngCriteria6, WS4.Range("U3"))
    Next m
    
    'release the range object
    Set rngCriteria5 = Nothing
    Set rngCriteria6 = Nothing

Is there a way to put this into an array to improve efficiency (and therefore time)?

Thanks a lot in advance!

E.

0 Answers
Related