Perfect Macro Combinations

[ytayta555]

I am the first time here , with this ocasion i say HALOO all , you are
ALL OK ; 2003 , XP;
Here is a clever Macro which give you the combinations of 90 things
taken by 15 ; The 90 individual piece values are assumed to be in
cells A1:A90 , Combins 90 will place the various combinations in
column C, (and this is the problem!) . Here is the Macro :


Sub Combins90()


Dim i1 As Long
Dim i2 As Long
Dim i3 As Long
Dim i4 As Long
Dim i5 As Long
Dim i6 As Long
Dim i7 As Long
Dim i8 As Long
Dim i9 As Long
Dim i10 As Long
Dim i11 As Long
Dim i12 As Long
Dim i13 As Long
Dim i14 As Long
Dim i15 As Long
Dim i16 As Long
Dim i17 As Long
Dim i18 As Long
Dim i19 As Long
Dim i20 As Long
Dim i21 As Long
Dim i22 As Long
Dim i23 As Long
Dim i24 As Long
Dim i25 As Long
Dim i26 As Long
Dim i27 As Long
Dim i28 As Long
Dim i29 As Long
Dim i30 As Long
Dim i31 As Long
Dim i32 As Long
Dim i33 As Long
Dim i34 As Long
Dim i35 As Long
Dim i36 As Long
Dim i37 As Long
Dim i38 As Long
Dim i39 As Long
Dim i40 As Long
Dim i41 As Long
Dim i42 As Long
Dim i43 As Long
Dim i44 As Long
Dim i45 As Long
Dim i46 As Long
Dim i47 As Long
Dim i48 As Long
Dim i49 As Long
Dim i50 As Long
Dim i51 As Long
Dim i52 As Long
Dim i53 As Long
Dim i54 As Long
Dim i55 As Long
Dim i56 As Long
Dim i57 As Long
Dim i58 As Long
Dim i59 As Long
Dim i60 As Long
Dim i61 As Long
Dim i62 As Long
Dim i63 As Long
Dim i64 As Long
Dim i65 As Long
Dim i66 As Long
Dim i67 As Long
Dim i68 As Long
Dim i69 As Long
Dim i70 As Long
Dim i71 As Long
Dim i72 As Long
Dim i73 As Long
Dim i74 As Long
Dim i75 As Long
Dim i76 As Long
Dim i77 As Long
Dim i78 As Long
Dim i79 As Long
Dim i80 As Long
Dim i81 As Long
Dim i82 As Long
Dim i83 As Long
Dim i84 As Long
Dim i85 As Long
Dim i86 As Long
Dim i87 As Long
Dim i88 As Long
Dim i89 As Long
Dim i90 As Long
Dim iRow As Long


iRow = 0


For i1 = 1 To 76
For i2 = i1 + 1 To 77
For i3 = i2 + 1 To 78
For i4 = i3 + 1 To 79
For i5 = i4 + 1 To 80
For i6 = i5 + 1 To 81
For i7 = i6 + 1 To 82
For i8 = i7 + 1 To 83
For i9 = i8 + 1 To 84
For i10 = i9 + 1 To 85
For i11 = i10 + 1 To 86
For i12 = i11 + 1 To 87
For i13 = i12 + 1 To 88
For i14 = i13 + 1 To
89
For i15 = i14 + 1
To 90
iRow = iRow +
1


Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _

+ Cells(i3, "A") + Cells(i4, "A") _

+ Cells(i5, "A") + Cells(i6, "A") _

+ Cells(i7, "A") + Cells(i8, "A") _

+ Cells(i9, "A") + Cells(i10, "A") _

+ Cells(i11, "A") + Cells(i12, "A") _

+ Cells(i13, "A") + Cells(i14, "A") _

+ Cells(i15, "A")
Next i15
Next i14
Next i13
Next i12
Next i11
Next i10
Next i9
Next i8
Next i7
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1



End Sub

My problem is the next :the number of combinations is very big over
45,000,000 000,000,000 of
combination , and this macro place the various combinations in Column
C only .My question is :
what can I do in this Macro, to place the result in the next Column
after it finish with Column C ?
how can I do to place the result in 100 of contiguous Columns (for
eg: "C:CC") ? Please very much to help me .

 

[Gary''s Student]

Right now you have:

iRow = 0

lots of stuff

iRow=iRow+1
Cells(iRow, "C") =.............

Instead, how about:

iRow=0
iCol=3

all the loop stuff

iRow=iRow+1
if iRow > 65536 then
iRow=1
iCol=iCol+1
endif
Cells(iRow, iCol) =.............


So when a column is filled, go to the top of the next column

 

[Dana DeLouis]

Would I be correct in that your output is too large, even for Excel 2007?

Number of Columns needed in Excel 2007:

=COMBIN(90,15)/POWER(2,20)

43,674,158,062

 

[ytayta555]

Gary"s Student , you are wontherfull ; YOU GIVED ME THE RIGHT
SOLUTION;
i tried hundreds of variants to resolve this problem;
Thank you very veeery much ;thanks allso and for Dana DeLouis
function ;
only one question if you have time :
,, How must look this Macro code , to place the result in
Columns ..., for eg : C to W ? "

Thank you very much

DDE

 

[RadarEye]

Hi,

I do not know the number of colums available in Excel 2007. but in
2003 it is only 256.
If you put the result on multiple worksheet you will need about
2,729,634,878 sheets.
I guess you get an Out of Memory error before you get there.

Never the less I have created a new macro combination which uses
recursion:

' ----- start code
Option Explicit

Private mDestCol As Long
Private mDestRow As Long
Private mCombinedSom As Long
Private mLastRow As Long
Private mLastCol As Long
Private mCombine As Long

Public Sub CombineMain()
Dim iMax As Long
Dim iCombine As Long
Dim iLoopMax As Long


iMax = Cells(1, 1).End(xlDown).Row

mCombine = 15

Cells(mCombine + 2, 2) = iMax

mDestCol = 3
mDestRow = 1

mLastRow = ActiveSheet.Rows.Count
mLastCol = ActiveSheet.Columns.Count

iLoopMax = iMax - mCombine + 1
mCombinedSom = 0

Application.ScreenUpdating = False
Call CombineLoop(1, iLoopMax, mCombine)

End Sub

Private Sub CombineLoop(ByVal pStart As Integer, _
ByVal pEnd As Integer, _
ByVal pDepth As Long)
Dim iLoop As Long

If pDepth = 0 Then
Cells(mDestRow, mDestCol) = mCombinedSom
mDestRow = mDestRow + 1
If mDestRow > mLastRow Then
Application.ScreenUpdating = True
Debug.Print Time
Application.ScreenUpdating = False
mDestCol = mDestCol + 1
mDestRow = 1
If mDestCol > mLastCol Then
MsgBox "The sheet is to small", _
vbOKOnly + vbCritical, _
"Can not complete this task"
End If
End If
Else
For iLoop = pStart To pEnd
mCombinedSom = mCombinedSom + Cells(iLoop, 1)
Call CombineLoop(iLoop + 1, pEnd + 1, pDepth - 1)
mCombinedSom = mCombinedSom - Cells(iLoop, 1)
Next
End If
End Sub

' ------ code end

On my computer:
INter(R) Core(TM) Duo CPU @ 1.73 Ghz
2 MB memory
Windows Vista Home Basic
Excel 2003

It take about 5 seconds for each column of 65536 results.
With only 253 columns available on the first sheet it will take about
21 minutes to fill is.

Are you sure you want to get the enormous number of results?

 

[ytayta555]

It take about 5 seconds for each column of 65536 results.
With only 253 columns available on the first sheet it will take about
21 minutes to fill is.

Are you sure you want to get the enormous number of results?

HI RadarEye , thank you for your Macro , i think I'll learn a lot
from it ! My big problem was that i couldn't get the result in another
columns , and Gary"s Student gived me the perfect solutions ;
to get the enormous number of results it isn't so important for me ,
i needed some few miliones of combinations ; in A1:A90 you can
type references for a function and then you can very easy transform
this references in functions . For eg : how long take you to make
65536
of Count function with the references of this function taken 90/15 in
combinatoric order ?
Imagine you need them very strong !..
With Macro Combin90 you can do this in 3 minutes ; I looked for
this thing many month .I think i'll post another subiect about :
,,How to make (if this thing can be create) an formula/function [eg:
Count],
which with autofill action , to fill the cells with combinatoric
references
based on the first reference you put in first cell ?" [mean :
B1,B2.....B14,B15]
I think is not a easy thing , ... here is the time of teachers ! ...

RESPECTFULLY FOR ALL