Loop Help

[jmenche]

Howdy,

I am looking for a little help in making the attached spreadsheet more efficient. What the spreadsheet does is perform calculations on each unique combination of products based on the sheet name. For example, the 'Results3' contains every unique combination of 3 products (field names) from the Data tab. You'll see that I had to create a separate procedure for each combination. The only difference is an additional layer of nesting. Does anyone know a way to make this more dynamic?

Another pitfall of this kind of analysis is memory. Depending on how big the data set is, the number of permutations can reach into the millions. I do not work with arrays much but I have them in there and dimmed them with a huge number of rows. Is there a better way to handle the memory?

Thanks for any help

Jeff

[mdmackillop]

As a starter:
You can ReDim your arrays to suit data size if you configure them correctly. (You can only redim the second size). The array can be transposed and written in one operation


Sub Sample()
Dim varResults()
ReDim varResults(3, 1000000)
For i = 0 To 3
For j = 0 To 100
varResults(i, j) = i + j
Next j
Next i
ReDim Preserve varResults(3, 100)
Cells(4, 4).Resize(101, 4) = Application.Transpose(varResults)
End Sub