WorksheetFunction.Sumifs with Absolute references...

[FuNeS13]

I have this code:

Dim Data_LR As Long
Dim Revision_LR As Long
Dim TheRange As Range
Dim Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7 As Range

Data_LR = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Revision_LR = Sheets("Revision").Cells(Rows.Count, "B").End(xlUp).Row

Set Arg1 = ActiveWorkbook.Sheets("Data").Range("R2C22:R" & Data_LR & "C11")    ' Sum Range
Set Arg2 = ActiveWorkbook.Sheets("Data").Range("C2:C" & Data_LR)    ' Material # range
Set Arg3 = ActiveWorkbook.Sheets("Revision").Range("B8")            ' Material Ref Range
Set Arg4 = ActiveWorkbook.Sheets("Data").Range("F2:F" & Data_LR)    ' Year Range
Set Arg5 = ActiveWorkbook.Sheets("Revision").Range("G5")            ' Year
Set Arg6 = ActiveWorkbook.Sheets("Data").Range("H2:H" & Data_LR)    ' Week Range
Set Arg7 = ActiveWorkbook.Sheets("Revision").Range("G7")            'Week Number

Set TheRange = Sheets("Revision").Range("G8:N" & Revision_LR)

TheRange.Value = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7)

That I want to make sure it works as this formula:

=SUMIFS(Data!$K$2:$K$112554, Data!$C$2:$C$112554, Revision!$B8, Data!$F$2:$F$112554, Revision!G$5, Data!$H$2:$H$112554, Revision!G$7)

So in my code I need to include the absolute references, but I'm not sure how to do that... Has anyone done this before? I personally don't want to use a loop but if there is no other option... Guess I'll settle for that....

 

[FuNeS13]

I have this code:

Dim Data_LR As Long
Dim Revision_LR As Long
Dim TheRange As Range
Dim Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7 As Range

Data_LR = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Revision_LR = Sheets("Revision").Cells(Rows.Count, "B").End(xlUp).Row

Set Arg1 = ActiveWorkbook.Sheets("Data").Range("R2C22:R" & Data_LR & "C11")    ' Sum Range
Set Arg2 = ActiveWorkbook.Sheets("Data").Range("C2:C" & Data_LR)    ' Material # range
Set Arg3 = ActiveWorkbook.Sheets("Revision").Range("B8")            ' Material Ref Range
Set Arg4 = ActiveWorkbook.Sheets("Data").Range("F2:F" & Data_LR)    ' Year Range
Set Arg5 = ActiveWorkbook.Sheets("Revision").Range("G5")            ' Year
Set Arg6 = ActiveWorkbook.Sheets("Data").Range("H2:H" & Data_LR)    ' Week Range
Set Arg7 = ActiveWorkbook.Sheets("Revision").Range("G7")            'Week Number

Set TheRange = Sheets("Revision").Range("G8:N" & Revision_LR)

TheRange.Value = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7)

That I want to make sure it works as this formula:

=SUMIFS(Data!$K$2:$K$112554, Data!$C$2:$C$112554, Revision!$B8, Data!$F$2:$F$112554, Revision!G$5, Data!$H$2:$H$112554, Revision!G$7)

So in my code I need to include the absolute references, but I'm not sure how to do that... Has anyone done this before? I personally don't want to use a loop but if there is no other option... Guess I'll settle for that....

this first part should be Range("H2:H" & Data_LR) instead of Range("R2C22:R" & Data_LR & "C11"), I was trying something else lol