Run calculations off a temporary array.

[CBI_North]

I have recently been learning to use a temporary array to collect and transfer data. Here’s a typical sample . . .

xCnt = WorksheetFunction.CountA(rng) ' Count of Variables, Height of Array.
yCnt = 2 ' Width of array, 2 fields.
ReDim TempArray(1 To xCnt, 1 To yCnt) ' Re-dimension the array.
i = 0

For c = c1 To c2 ' Fill Array with measure values.
xDesc = wks_s.Cells(r1, c).Value
If xDesc <> "" Then
i = i + 1
xVal = wks_s.Cells(r, c).Value
TempArray(i, 1) = xDesc
TempArray(i, 2) = xVal
End If
Next

wks_m.Activate ' Paste array values into measures sheet.
Set xOutput = wks_m.Range(Cells(4, 1), Cells(4 + xCnt - 1, 1 + yCnt - 1))

I’m wondering if there’s any way to run some basic calc’s off the data within the array, without having to transfer it into a spreadsheet? Such as sum the xVal, or pull a SumIf on different values of the xDesc? I’m interested in using this in a user defined function. Any ideas?

 

[RalphA]

As is the custom here, i welcome you to this board. Here are my thoughts on your post:

Just bfore the End If, add this line to get your sum of the xVal values:
xValTot = xValTot + xVal

And, for the SumIf on different values of the xDesc. Say you want the sum of all values of xDesc tha are greater than 50. Use:
IF xDesc > 100 THEN
xDescTotGT100 = xDescTotGT100 + xDesc
END IF