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?

https://stackoverflow.com/questions/24456328/creating-and-transposing-array-in-vba/38251589 — Tragamor, Nov 02 '21 at 08:39
The array will only exist for the length of the function and is not returned from that function so for all intents and purposes does not exist to include into your later code. You should be able to return as a variant for later use in code — Tragamor, Nov 02 '21 at 08:45

score 1 · Accepted Answer · answered Nov 02 '21 at 09:56

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

I hadn't looked into using sumproduct instead, thank you – Drawleeh Nov 02 '21 at 12:56 — Drawleeh, Nov 02 '21 at 12:56

How do I use an Array as a range?

1 Answers1

Sum Products With Condition