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?
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?