hi all,
I'm trying to loop through several rows and columns using application.worksheetfunction.sumifs. Here is the segment I'm having trouble with:
Dim lcol As Long
Dim rrow as Long
Dim SumRange As Range
Dim Criteria1 As Range
Dim Criteria2 As Range
lcol = Cells(1, Columns.count).End(xlToLeft).Column
rrow = range("A" & rows.count).End(xlUp).Row
Set SumRange = Worksheets(Gs).Range("F2:F" & grow)
Set Criteria1 = Worksheets(Gs).Range("A2:A" & grow)
Set Criteria2 = Worksheets(Gs).Range("C2:C" & grow)
Range("B2").Select
For c = 2 To lcol
For i = 2 To rrow
Worksheets(Rs).Range(i, c) = Application.WorksheetFunction.SumIfs(SumRange, Criteria1, Range("A" & i), Criteria2, Range(c, i))
Next
Next
I'm getting the error message "Method 'Range' of object'_Global' failed" on the last part in red above.
Also if there is a better way than looping I'm all ears. I don't like looping, it takes too long. PLEASE HELP.
Thank you
I'm trying to loop through several rows and columns using application.worksheetfunction.sumifs. Here is the segment I'm having trouble with:
Dim lcol As Long
Dim rrow as Long
Dim SumRange As Range
Dim Criteria1 As Range
Dim Criteria2 As Range
lcol = Cells(1, Columns.count).End(xlToLeft).Column
rrow = range("A" & rows.count).End(xlUp).Row
Set SumRange = Worksheets(Gs).Range("F2:F" & grow)
Set Criteria1 = Worksheets(Gs).Range("A2:A" & grow)
Set Criteria2 = Worksheets(Gs).Range("C2:C" & grow)
Range("B2").Select
For c = 2 To lcol
For i = 2 To rrow
Worksheets(Rs).Range(i, c) = Application.WorksheetFunction.SumIfs(SumRange, Criteria1, Range("A" & i), Criteria2, Range(c, i))
Next
Next
I'm getting the error message "Method 'Range' of object'_Global' failed" on the last part in red above.
Also if there is a better way than looping I'm all ears. I don't like looping, it takes too long. PLEASE HELP.
Thank you