Variable sumifs sum range

Question

I get a

Compile error: Type Mismatch

error on my code. The idea behind the code is to have a variable sum range depended on the j loop. Please don't criticize me as I am still very new to VBA. Please help.

Sub eiegraanGB()
    Application.ScreenUpdating = False

    Dim i As Integer
    Dim j As Integer
    Dim arg1 As Range
    Dim arg2 As Range
    Dim arg3 As Range
    Dim arg4 As Range
    Dim arg5 As Range
    Dim arg6 As Range
    Dim arg7 As Range
    Dim arg8 As Range
    Dim arg9 As Range
    Dim arg10 As Range
    Dim arg11 As Range
    Dim arg As Integer
    Dim k As Range

    Worksheets("HR Grn Bedryf").Activate

    Set arg2 = Worksheets("Sorted Data").Range("I:I")
    Set arg3 = Worksheets("Sorted Data").Range("M:M")
    Set arg4 = Worksheets("Sorted Data").Range("O:O")
    Set arg5 = Worksheets("Sorted Data").Range("N:N")
    Set arg6 = Worksheets("Sorted Data").Range("P:P")
    Set arg7 = Worksheets("Sorted Data").Range("J:J")
    Set arg8 = Worksheets("Sorted Data").Range("A:A")
    Set arg10 = Worksheets("Sorted Data").Range("B:B")

    For j = 2 To 7
        Set k = arg & j
        For i = 184 To 2386
            Set arg9 = Cells(i, 1)
            Set arg11 = Cells(i, 12)
            Cells(i, 1).Select
            If ActiveCell.Value = "BVH EIE GRAAN" Or ActiveCell.Value = "AANKOPE EIE REK. GRN" Or ActiveCell.Value = "EVH EIE GRAAN" Or ActiveCell.Value = "VERKOPE EIE GRAAN" Then
                Cells(i, j) = Application.WorksheetFunction.SumIfs(k, arg8, arg9, arg10, arg11)
            Else

            End If  
        Next i    
    Next j

    Application.ScreenUpdating = True
End Sub

`Set k = arg & j` doesn't work like that in VBA. Make an array of ranges if you want to loop through. — , Jul 09 '18 at 07:45

score 1 · Accepted Answer · answered Jul 09 '18 at 07:47

You need to use an array for this. Define arg as an array from 1 to 11:

Dim arg(1 To 11) As Range
Set arg(2) = Worksheets("Sorted Data").Range("I:I")

And then you can use it like this:

Set k = arg(j)

Note: I recommend to read How to avoid using Select in Excel VBA to make your code faster and much more stable.

Also I suggest to use Long instead of Integer Excel has more rows than Integer can handle and there is no benefit in using Integer in VBA at all.

Variable sumifs sum range

1 Answers1