Re: Loop to run macro each tow

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 <pgjigno...@xxxxxxxxxx> 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


.

Relevant Pages

  • Re: Can I use VBA to create formulas in cells?
    ... Reverse the use of the loop counters in the .Offsetto reverse layout. ... Dim SL As Integer ' SheetName Loop ... 'choose cell where you want ... This will result in a sheet with approxe 100x200 cells ...
    (microsoft.public.excel.misc)
  • RE: Using VBA to Create New Sheet That Consolidates Others
    ... Add this just after the summary sheet has been added...... ... Dim sh As Worksheet ... 'loop through all worksheets and copy the data to the DestSh ...
    (microsoft.public.excel.misc)
  • Help Required By Relative Newcomer
    ... the following will loop through your range and change cell ... A1 on your second sheet ... dim source_wks as worksheet ... Dim cell as range ...
    (microsoft.public.excel.programming)
  • Re: code question
    ... > you removed my loop, it only made changes to the january sheet. ... >> Sub Add_User ... >>> Dim uName As String ...
    (microsoft.public.excel.programming)
  • Re: Loop to run macro each tow
    ... J has the value 9 on line 4 and during the rest of the sub. ... Sheet named Base ... I would like if possible a loop on the sheet bases with macro Ci below. ... Dim A As Integer, B As Integer, C As Integer ...
    (microsoft.public.excel.programming)