ExcelBanter - To adapt macro for relative lines

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - To adapt macro for relative lines (https://www.excelbanter.com/excel-programming/389973-adapt-macro-relative-lines.html)

To adapt macro for relative lines

for cell A1

For J = 1 To 8

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=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

To adapt macro for relative lines

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

To adapt macro for relative lines

I think the problem is the line wrapping. make sure code is on one line or
use _ for line continuation.

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=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,c olumnoffset:=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

To adapt macro for relative lines

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,c olumnoffset:=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

To adapt macro for relative lines

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 procedu
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 line or
use _ for line continuation.

vN(J) =
Sheets("feuil3").range("A1").offset(rowoffset:=J,c olumnoffset:=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,c olumnoffset:=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