How do I use an Array as a range?

Question

Might be a dumb question to begin with, but I need to store data within an array, and then use that data in a function that calls for a range.

I have the following code for my array -

Function UnitCheckArr()
Dim UnitValueArr(2 To 250) As Long
Dim UnitValue As Long


For UnitValue = LBound(UnitValueArr) To UBound(UnitValueArr)
UnitValueArr(UnitValue) = Cells(UnitValue, 4) * Cells(UnitValue, 6)
Next UnitValue

End Function

And then I would like to use said array inside a SUMIF function, something like this -

Sub NetSumIF()
If [COUNTA(F2:F250)=0] Then

Worksheets("Sheet1").Range("K2:K250") = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("I2:I250"), "I2", Worksheets("Sheet1").Range("UnitCheckArr"))

End If
End Sub

Currently I'm getting an object defined error and I assume its because I can't express an array as a range for the sumif..? I can't express the original function into the worksheet. Any ideas how to fix this or tackle it differently?

Answer 1

Sum Products With Condition

• SumIf works with ranges, not with arrays.
• Range("A1:A10") is a range while Range("A1:A10").Value is a 2D one-based one-column array (containing 10 elements (rows)).
• You could write the products to another column and apply the SumIf using this column, or you could create loops in the code (probably too slow), or whatnot.
• Rather try one of the following. Let us know what's wrong with it and if you need some parts of it dynamic.

Excel

• In cell K2 you could use:

  =IFERROR(SUMPRODUCT(--(I$2:I$250=I2),D$2:D$250,F$2:F$250),"")
  

  and copy down.

VBA (Formula)

Option Explicit

Sub SumData()
    Dim dFormula As String
    Const dFormula As String _
        = "=IFERROR(SUMPRODUCT(--(I$2:I$250=I2),D$2:D$250,F$2:F$250),"""")"
    With ThisWorkbook.Worksheets("Sheet1").Range("K2:K250")
        .Formula = dFormula
        .Value = .Value
    End With

End Sub