Hi,

*i hope someone out there can understand the convoluted explanatio
i've written below... but, to cut to the chase, i'm looking to use
worksheetfunction (like countif & average) on part or all of an array
is that even possible or, if not, what alternatives do i have???*

I have a huge amount of data that excel is unwilling to store for m
(very long numbers in 10,000columns x 10,000rows) so I've decided t
move it all into an array so that I can summarise it from there. And
couldn't save it as an array matrix of 10000x10000 elements because
don't know how to reference a specific _range_ of elements within th
array [as you would a range of cells on a sheet... e.g. Range(A1:A3) o
Range(Cells(1,1),Cells(3,1))].

So my solution was to save each 'column' of data into its own singl
dimension array (since I only need to access the 'columns' as a whole
not certain parts of them) and then save each of those arrays as one o
the 10000 elements in an 'umbrella array' (i.e. an array of arrays). Fo
example (tho this obviously isn't a very efficient example):

Code
-------------------
'first fill each of the 'column' arrays
arr1 = (1,2,3...,10000)
arr2 = (1,2,3...,10000)
.....
arr10000 = (1,2,3...,10000)
'then move each of those into the 'umbrella array' in turn
umbArr(1) = arr1
umbArr(2) = arr2
....
umbArr(10000) = arr1000
-------------------

You see, doing this lets me refer to the element arr2(513) by sayin
umbArr(2)(513). And I thought that it would also let me use th
'columns' (arr1, arr2 etc) from WITHIN WorksheetFunctions. But it's no
working...

Specifically, I need to calculate the number of elements in eac
'column' of the matrix (=each arr1-arr10000 array) that equal or excee
the first element in that array. If I were dealing with cells on
worksheet, then I could use something like:

Code
-------------------
NumExtreme = WorksheetFunction.CountIf(Range(Cells(1,2),Cells(1 0000,2)),Range(1,2)) + WorksheetFunction.CountIf(Range(Cells(1,2),Cells(1 0000,2)), "" & Range
-------------------

But VBA won't let me do this:

Code
-------------------
NumExtreme = WorksheetFunction.CountIf(arr2, arr2(1)) + WorksheetFunction.CountIf(arr2, "" & arr2(1)
-------------------
Or this:

Code
-------------------
NumExtreme = WorksheetFunction.CountIf(umbArr(2), umbArr(2)(1)) + WorksheetFunction.CountIf(umbArr(2) , "" & umbArr(2)(1)
-------------------

So, other than temporarily loading all of those values into a workbook
using a CountIf on the cells, and then erasing those cells once I have
result, I don't know how to solve this dilemma... I'm stuck :(

Any suggestions you can give would be most appreciated.
Thanks so much,

--
fer
-----------------------------------------------------------------------
fern's Profile: http://www.excelforum.com/member.php...nfo&userid=985
View this thread: http://www.excelforum.com/showthread.php?threadid=39372