Skipping Elements in an Array.

[vbsjive]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to accomplish combining elements from one range to that of another range and placing the result into one cell.<o></o>
<o></o>
Here is the code im working with:<o></o>
<o></o>
Sub CommandButton6kl4_Click()<o></o>
Dim Rng1 As Range<o></o>
Dim Rng2 As Range<o></o>
Dim ray(1 To 10, 1 To 2359)<o></o>
Dim Rw As Integer<o></o>
Dim AC As Integer<o></o>
Set Rng1 = Range("b1:b10")<o></o>
Set Rng2 = Range("A441:j499")<o></o>
For Rw = 1 To 10<o></o>
For AC = 1 To 2359<o></o>
ray(Rw, AC) = IIf(Rng1.Cells(1, Rw) = 0 Or Rng2.Cells(1, AC) = 0, 0, IIf(Rng1.Cells(1, Rw) > Rng2.Cells(1, AC) Or Rng1.Cells(1, Rw) = Rng2.Cells(1, AC), 0, Rng1.Cells(1, Rw) & Rng2.Cells(1, AC)))<o></o>
Next AC<o></o>
Next Rw<o></o>
Range("K2705:T5064") = ray<o></o>
End Sub<o></o>
<o></o>
There are lots of range values I am working with that I won't need as they contain Zeros. I want a function that skips over all Zeros in each Range, so that all combinations of each range are realized. <o></o>
<o></o>
So Rng1 as defined above has 10 possible elements and Rng2 (is an area) has 3481 elements.<o></o>
<o></o>
If Rng1 is say: 0,3,40,565,965,0,0,1,2,16<o></o>
<o></o>
and Rng2 is say: 21,0,4,0,0,0,0,0,etc,etc,etc<o></o>
<o></o>
I want the output to be: 321,34,3etc,3etc until the routine has assigned each possible combination a place in the array.<o></o>
<o></o>
It’s crucial that only elements greater than zero are combined and that no elements are output in their original state (no values in either range can combine with 0)<o></o>
<o></o>
Also Rng1 is defined as linear column where as Rng2 is an area. Not sure if this screws it up at runtime.<o></o>
<o></o>
Running this a couple of times I often end up with the #N/A output, with the correct array size and a bunch of zeros I don't want.<o></o>
<o></o>
Also, you might be wondering why the array has to be this big. The ranges themselves are dynamic so the position of the zero values change, thus changing how many elements the array could be filled with. It’s possible the array could be filled to its maximum capacity or with nothing at all.<o></o>
<o></o>
<o></o>

Stumped, Please help.<o></o>
<o></o>

Thanks!<o></o>
<o></o>

 

[Jerry Sullivan]

Hi vbsjive,

Also, you might be wondering why the array has to be this big. The ranges themselves are dynamic so the position of the zero values change, thus changing how many elements the array could be filled with. It’s possible the array could be filled to its maximum capacity or with nothing at all.

You might consider using one or more dynamic arrays instead of a static array that is sized for your worst case scenario.

Perhaps you create a one-dimensional array for each range and populate them with just the greater than zero values. Then you could use two For...next statements to make all the combinations.

That would be more efficient since you would only have to remove the zero values once instead of repeating that process for each value in Rng2 each time you step to your next Rng1 value.