Hello

I have a sheet with a series of figures with which I want to make
combinations.

Approximately 200 lines

Sheet named Base

Between each series, the blank rows are planned for the combinations
of the figures of the various lines.

Ex : combin(8;6) 28 Blank rows

Row 1
28 Blank rows
Row 2
28 Blank rows
Row 3
28 Blank rows
ETC..

the combinations must be offset(0,5) of the derniere nonempty cell of
columns A: to O:
of each line

I would like if possible a loop on the sheet bases with macro Ci below.
It only functions with the first line.

Thank you

Option Explicit
Option Base 1
Sub Combin_6Num()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 10

vN(J) = Cells(J).Value

Next

J = J - 1



Cells(1, 37).Select

I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select

End Sub

in sheet base i have a lot series of 8 numbers, i want to select for
each rows, select the column O,run the macro with the series of number
of the row.
combin(10,8)=28 combinations 28 Blank rows

I think has for X = 1 to step 28 select the series of numbers of the
row run the macro
ex:
row 10 1 2 3 4 5 6 7 8 the macro be run with that bumber colum O

row 38 4 5 12 14 15 16 the macro be run with that bumbercolum O

it is to compare the result with combinations of another sheet




PST a écrit :


Hello

I have a sheet with a series of figures with which I want to make
combinations.

Approximately 200 lines

Sheet named Base

Between each series, the blank rows are planned for the combinations
of the figures of the various lines.

Ex : combin(8;6) 28 Blank rows

Row 1
28 Blank rows
Row 2
28 Blank rows
Row 3
28 Blank rows
ETC..

the combinations must be offset(0,5) of the derniere nonempty cell of
columns A: to O:
of each line

I would like if possible a loop on the sheet bases with macro Ci below.
It only functions with the first line.

Thank you

Option Explicit
Option Base 1
Sub Combin_6Num()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False


For J = 1 To 10

vN(J) = Cells(J).Value

Next

J = J - 1



Cells(1, 37).Select

I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select


Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select

End Sub

Hi
It is a lot of looping to make sense of! Can it be simplified?
I saw 2 things:
1. There is no loop on J. It has the value 9 on the 4th row and does
not change again. Is that what you want?
2. With J = 9 there are 6 loops of 4 rounds each and each time you
fill in 6 column values and offset 1 row down. This would give you 4
to the power of 6 = 4096 row offsets. I increments after each row to
I = 4097 when all looping has finished, and never has a value of
6001, so your select case does not happen. Is this what you want?

regards
Paul


On May 17, 5:34 am, PST wrote:
Hello

I have a sheet with a series of figures with which I want to make
combinations.

Approximately 200 lines

Sheet named Base

Between each series, the blank rows are planned for the combinations
of the figures of the various lines.

Ex : combin(8;6) 28 Blank rows

Row 1
28 Blank rows
Row 2
28 Blank rows
Row 3
28 Blank rows
ETC..

the combinations must be offset(0,5) of the derniere nonempty cell of
columns A: to O:
of each line

I would like if possible a loop on the sheet bases with macro Ci below.
It only functions with the first line.

Thank you

Option Explicit
Option Base 1
Sub Combin_6Num()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False

For J = 1 To 10

vN(J) = Cells(J).Value

Next

J = J - 1

Cells(1, 37).Select

I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select

Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select

End Sub

Hi
Some things I saw:
1. J has the value 9 on line 4 and during the rest of the sub. Is it
supposed to loop too?
2. You have 6 loops of 4 turns each and 1 row offset in each one. This
will give 4 to the power of 6 = 4096 row offsets and I will have a
final value of 4097. Your select case will then not apply as I must be
at least 6001.
regards
Paul

regards
Paul
On May 17, 5:34 am, PST wrote:
Hello

I have a sheet with a series of figures with which I want to make
combinations.

Approximately 200 lines

Sheet named Base

Between each series, the blank rows are planned for the combinations
of the figures of the various lines.

Ex : combin(8;6) 28 Blank rows

Row 1
28 Blank rows
Row 2
28 Blank rows
Row 3
28 Blank rows
ETC..

the combinations must be offset(0,5) of the derniere nonempty cell of
columns A: to O:
of each line

I would like if possible a loop on the sheet bases with macro Ci below.
It only functions with the first line.

Thank you

Option Explicit
Option Base 1
Sub Combin_6Num()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False

For J = 1 To 10

vN(J) = Cells(J).Value

Next

J = J - 1

Cells(1, 37).Select

I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select

Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select

End Sub

Not I want, just that the macro begin the combination in the column O of
each ligne.
J' have the macro one for inserer the lines.

This code is for line 1.
For J = 1 To 10 vN(J) = Cells(J).Value Next J = J - 1

I want just that they adapts for each ligne.(ex:Activecell.row.area)
If necessary in another Sub()

Ex: While making for 1 to 2900 step 29, I want to launch macro the 100
times for 100 lines with 100 series of 28 combinations

Sorry if my explanations not be clear

Thank you for your reply


a écrit :
Hi
Some things I saw:
1. J has the value 9 on line 4 and during the rest of the sub. Is it
supposed to loop too?
2. You have 6 loops of 4 turns each and 1 row offset in each one. This
will give 4 to the power of 6 = 4096 row offsets and I will have a
final value of 4097. Your select case will then not apply as I must be
at least 6001.
regards
Paul

regards
Paul
On May 17, 5:34 am, PST wrote:
Hello

I have a sheet with a series of figures with which I want to make
combinations.

Approximately 200 lines

Sheet named Base

Between each series, the blank rows are planned for the combinations
of the figures of the various lines.

Ex : combin(8;6) 28 Blank rows

Row 1
28 Blank rows
Row 2
28 Blank rows
Row 3
28 Blank rows
ETC..

the combinations must be offset(0,5) of the derniere nonempty cell of
columns A: to O:
of each line

I would like if possible a loop on the sheet bases with macro Ci below.
It only functions with the first line.

Thank you

Option Explicit
Option Base 1
Sub Combin_6Num()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(10) As Integer
Application.ScreenUpdating = False

For J = 1 To 10

vN(J) = Cells(J).Value

Next

J = J - 1

Cells(1, 37).Select

I = 1
For A = 1 To J - 5
For B = A + 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(A)
ActiveCell.Offset(0, 2).Value = vN(B)
ActiveCell.Offset(0, 3).Value = vN(C)
ActiveCell.Offset(0, 4).Value = vN(D)
ActiveCell.Offset(0, 5).Value = vN(E)
ActiveCell.Offset(0, 6).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001
ActiveCell.Offset(-60000, 8).Select
End Select

Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Select

End Sub