VBA to create n arrays where each array is based on a subset of values in a named range

[campione]

Hi guys

I would greatly appreciate if someone could help me with the coding for the following:
- use a loop to create n arrays in VBA where each array is based on a descending number of values in a named range

So assuming my named range has 10 values,
- the first array is simply the original named range with 10 values
- the second arrray will exclude the 1st value in the named range, but include the remaining 9
- the third array will exclude the 1st TWO values in the named range, but include the remaining 8 and so on

I've been trying for hours but can't get it right?

I tried to base the coding on the resize formula, but it crashed and burned.
For i = n
Arr(i) = Range("Test").Resize(i, 1)
Next i

But I think that coding applied to element i in Array Arr as opposed to creating Arr i
Any ideas?

 

[hateme28]

why do you need 10 different arrays? if you have 1 array with all of the information within the range. you can reference any part of that array instead of creating 10 different arrays.

 

[campione]

why do you need 10 different arrays? if you have 1 array with all of the information within the range. you can reference any part of that array instead of creating 10 different arrays.

Your idea might be much more efficient. If you pull a named range into an array, how do I reference the first 10 values in an array to use them in say a sumproduct? I know how to reference a specific element in an array but not a subset.

I need to be able to loop through and use subsets of the array in calculations: the first element, then the first two, the first 3, etc. I will use the subsets in UDFs and eventually output the answers via an array UDF formula

 

[campione]

Your idea might be much more efficient. If you pull a named range into an array, how do I reference the first 10 values in an array to use them in say a sumproduct? I know how to reference a specific element in an array but not a subset.

I need to be able to loop through and use subsets of the array in calculations: the first element, then the first two, the first 3, etc. I will use the subsets in UDFs and eventually output the answers via an array UDF formula

I read on another forum from a similar post that:
"There is no way to pass a subset of an array to a function or a worksheet function just using VBA. VBA does not provide methods to manipulate subsets of arrays or to pull out subsets of arrays. You can build a subset of an array using index.

Personally, I would just loop through the array and build a new array that is the subset of the existing array."

Do you guys agree?
How can I loop through an array and build new arrays where first new array is the first 1st value, then second array is the first 2 values and so on?

 

[hateme28]

What exactly are you trying to do? what do you have so far?

 

[campione]

What exactly are you trying to do? what do you have so far?

Hi hateme28

I have a UDF which uses two ranges as inputs: a column containing returns and column containing weights.
Assume there are 10 values in each column, I want to loop through and do a series of sumproducts on different subsets of the ranges.

The first iteration of the loop will do a sumproduct of the top weight with the bottom return, the second iteration will so a sum product of the top 2 weights and the bottom 2 returns, and so on.
The last nth iteration of the loop will do a sumproduct of all the weights and returns.

I am stuck because I don't know how to reference a subset of an array so that I can use the first n values of the array/range in a UDF or worksheetfunction.

I've read that it's not possible in Excel VBA. Someone else said it's possible if you use the GetMethod.