Run calculations off a temporary array.

CBI_North

New Member
Joined
Sep 22, 2006
Messages
1
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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
 

Forum statistics

Threads
1,136,267
Messages
5,674,729
Members
419,523
Latest member
Urnovio

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top