I give you the whole code for test if possibele, to put some rows of
numbers in feuil3 A:H run macro_Combs
I tried to find, sent messages, nobody gave me the answer
With these three macros,I would like the result feuil3,
by taking the series of figures of the feuillle feuil3 A1:H200
test_Insert_Rows()
Combin_6N()
macro_Combs()
I would like that this part holds account of active line
>>>>>>>>
For J = 1 To 8
vN(J) = Sheets("feuil3").Cells(J).Value 'J avant
Next
>>>>>>>
My procedure:
1) run test_Insert_Rows() for inserer 28 blank line between each line of
feuil3
2) run macro_Combs() which calls Combin_6N() all the 28 lines and puts
the combinations
which correspond to the rows of the line in progress in the 28 following
rows.
Thus if I have 100 series of numbers, I would have 100 rows X 28
combinations by lines
> > > 2800 rows of different combinations.
Option Explicit
Public Lig As Long
Public Col As Long
Public numRows As Integer
Option Base 1
1)
Sub Combin_6N()
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 8
vN(J) = Sheets("feuil3").Cells(J).Value 'J avant
Next
J = J - 1
ActiveCell.Offset(0, 17).Select
'Range("a32").Select ' Pour deplacer les combinaisons Ã* l'endroit souhaité
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
2)
Sub test_Insert_Rows()
Dim r As Integer
Sheets("Base").Activate
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 28
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
End Sub
3)
Sub macro_Combs()
For Lig = 1 To 100 Step 28
For Col = 1 To 1
Next
Cells(Lig, 17).Select
Call Combin_6N
Next
End Sub
Joel a écrit :
> I think the problem is the line wrapping. make sure code is on one lineor
> use _ for line continuation.
>
> vN(J) =
> Sheets("feuil3").range("A1").offset(rowoffset:=J,columnoffset:=0).Value
>
> "PST" wrote:
>
>> Thank you for you reply but
>> red line in vb editor
>>
>> for cell A1
>>
>>
>> Joel a écrit :
>>> for cell A1
>>>
>>> For J = 1 To 8
>>>
>>> vN(J) =
>>> Sheets("feuil3").range("A1").offset(rowoffset:=J,columnoffset:=0).Value
>>>
>>> Next
>>>
>>> "PST" wrote:
>>>
>>>> hello
>>>>
>>>> To adapt this part of macro below for relative lines
>>>>
>>>> For J = 1 To 8
>>>>
>>>> vN(J) = Sheets("feuil3").Cells(J).Value 'J avant
>>>>
>>>> Next
>>>>
>>>> Thank You
>>>>
>>>> Sub Combin_6N()
>>>> 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 8
>>>>
>>>> vN(J) = Sheets("feuil3").Cells(J).Value 'J avant
>>>>
>>>> Next
>>>>
>>>>
>>>>
>>>>
>>>> J = J - 1
>>>>
>>>> ActiveCell.Offset(0, 17).Select
>>>> 'Range("a32").Select ' Pour deplacer les combinaisons Ã* l'endroit souhaité
>>>> 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
>>>> Cells(Lig, 20) = "toto" & " " & Lig
>>>>
>>>> End Sub
>>>>
>>
Similar Threads
