I have been making a VBA for applying worksheetfunction.sumif.

The goal is to:

1. sumif multiple criteria
2. sumif values from other sheets
3. sumif result shown in a "report" sheet.

I got to a point where the worksheetfunction.sumif is applied to columns with sheet name. And items are sumif from the values of the sheet.

Sub createSUMIF()

    Dim ws As Worksheet, partlist As Worksheet
    Dim NoCol As Integer, NoRow As Integer
    Dim CritRng As Range, SumRng As Range

    Application.ScreenUpdating = False
    
    Set ws = Worksheets("Report")
    
    'dynamic cell value that refer to cell with worksheet name.
    Set partlist = Worksheets(Cells(2, c).Value)
    
    'setting where the rows and columns end
    NoRow = ws.Cells(ws.Cells.Rows.Count, 1).End(xlUp).Row
    NoCol = ws.Cells(2, ws.Cells.Columns.Count).End(xlToLeft).Column
    
    'setting dynamic partlist and set range to sumif
    Set CritRng = partlist.Range("A3:B1000")
    Set SumRng = partlist.Range("B3:B1000")
  
'start to end column & rows
For c = 2 To NoCol
For r = 3 To NoRow

    'applying the sumif
    Cells(r, c) = WorksheetFunction.sumif(CritRng, Cells(r, 1), SumRng)

Next r
Next c

    Application.ScreenUpdating = True

End Sub

However, the sumif was applied to the last column with sheet name. So all other sumif results were values from the last column with sheet name.

The troubles that I am facing:

1. finding a way to make the "partlist" to refer to cell value with sheet name
2. have worksheetfunction.sumif to apply to each column to designated sheet name

Thank you all in advance.

CodePudding user response：

The value of "c" in your partlist never gets updated within your For loop but remains 0. I replicated what you are doing on a smaller scale to visualize what is happening.

You'll need to set your partlist within the for loop