# Run calculations off a temporary array.

#### CBI_North

##### New Member
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?

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

Replies
3
Views
326
Replies
16
Views
394
Replies
5
Views
817
Replies
2
Views
252
Replies
3
Views
239

1,219,798
Messages
6,150,318
Members
450,951
Latest member
kh198

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back