array/for next loops

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
Hello folks,

Trying to figure out a way to have an array be looped and shifting items to the right as show in fig 1.

Figure1 lays out the pattern I'm trying to achieve via a macro with arrays and for next loop.

Yellow signifies the start of that "number group" for which we are looking for combinations of numbers.

so, each combo will have 100 cells, 10 rows showing possible combos.

So, if my thinking is correct I should have 1000 rows covering all combos by the time a "shift right" all the array "items" numbers I guess it would be.

fig1.
Paired Matches.xlsm
OPQRSTUVWX
112345678910
210123456789
391012345678
489101234567
578910123456
667891012345
756789101234
845678910123
934567891012
1023456789101
1123456789101
1212345678910
1310123456789
1491012345678
1589101234567
1678910123456
1767891012345
1856789101234
1945678910123
2034567891012
2134567891012
2223456789101
2312345678910
2410123456789
2591012345678
2689101234567
2778910123456
2867891012345
2956789101234
3045678910123
old1172022

I have it doing this fig 2 so far.
fig2.
Paired Matches.xlsm
OPQRSTUVWX
3312345678910
3412345678910
3512345678910
3612345678910
3712345678910
3812345678910
3912345678910
4012345678910
4112345678910
4212345678910
old1172022
 
I thought post 8 meant you had solved it, but I guess not. Will see if I can get into it tonight or tomorrow, or someone else might beat me to it.
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How about:

VBA Code:
Sub NumbersTest()
'
    Const MaxNumber As Long = 10
'
    Dim ArrayOffset                 As Long, ArrayRow       As Long
    Dim NumberArray(MaxNumber * 2)  As Variant, ResultArray As Variant
'
    ReDim ResultArray(1 To MaxNumber, 1 To MaxNumber)
'
    ArrayRow = 1
'
    For I = 1 To 2
        For J = 1 To MaxNumber
            NumberArray(ArrayRow) = J
            ArrayRow = ArrayRow + 1
        Next
    Next
'
    For I = 1 To MaxNumber
        For J = 1 To MaxNumber
            ResultArray(I, J) = NumberArray(J + ArrayOffset)
        Next
'
        ArrayOffset = NumberArray(J - I - 1)
    Next

    Range("O1").Resize(MaxNumber, MaxNumber) = ResultArray
End Sub
 
Upvote 0
Hello johnnyL, yes that's the ticket!

I'm just working on understanding what's going on 100%. May have questions later. Thanks all for your input its been fun...
 
Upvote 0
@zrx1200 See if this commented version helps out.

VBA Code:
Sub NumbersTest()
'
    Const MaxNumber As Long = 10                                            ' <--- Set this to the maximum integer desired ... 10 if you want 1 to 10
'
    Dim ArrayOffset                 As Long, ArrayRow       As Long
    Dim IntegerValue                As Long
    Dim LoadIntegersLoopCounter     As Long
    Dim ResultArrayColumnNumber     As Long
    Dim ResultArrayRow              As Long
    Dim NumberArray(MaxNumber * 2)  As Variant, ResultArray As Variant
'
    ReDim ResultArray(1 To MaxNumber, 1 To MaxNumber)                       ' Set Size of ResultArray ... Rows, Columns
'
    ArrayRow = 1                                                            ' Initialize ArrayRow to 1 ...
'
    For LoadIntegersLoopCounter = 1 To 2                                    ' Establish a loop to run 2 times
        For IntegerValue = 1 To MaxNumber                                   '   Establish a loop to run 1 to the maximum integer times
            NumberArray(ArrayRow) = IntegerValue                            '       Save the integer into NumberArray(ArrayRow)
            ArrayRow = ArrayRow + 1                                         '       Increment the NumberArray slot to have integers saved into
        Next                                                                '   Loop back for next integer
    Next                                                                    ' Loop back to repeat the process a 2nd time
'
'                                                                           ' NumberArray will now have a blank slot for NumberArray(0) & the range of integers
'                                                                           ' Two times in the rest of the NumberArray slots ie. If MaxNumber = 10 then ...
'                                                                           ' NumberArray(1) = 1, NumberArray(10) = 10, NumberArray(11) = 1, NumberArray(20) = 10
'
    For ResultArrayRow = 1 To MaxNumber                                     ' Establish first loop to save the rows for the 2D 1 Based ResultArray
        For ResultArrayColumnNumber = 1 To MaxNumber                        '   Establish 2nd loop to save the column values for the 2D 1 Based ResultArray
'
'           Save value from NumberArray to ResultArray
            ResultArray(ResultArrayRow, ResultArrayColumnNumber) = NumberArray(ResultArrayColumnNumber + ArrayOffset)
        Next                                                                '   Loop back for next column value to save
'
        ArrayOffset = NumberArray(ResultArrayColumnNumber - ResultArrayRow - 1) '   Adjust starting point of NumberArray values to be saved into ResultArray
    Next                                                                    ' Loop back for next row

    Range("O1").Resize(MaxNumber, MaxNumber) = ResultArray                  ' Display ResultArray to sheet
End Sub
 
Upvote 0
Solution
@zrx1200 See if this commented version helps out.

VBA Code:
Sub NumbersTest()
'
    Const MaxNumber As Long = 10                                            ' <--- Set this to the maximum integer desired ... 10 if you want 1 to 10
'
    Dim ArrayOffset                 As Long, ArrayRow       As Long
    Dim IntegerValue                As Long
    Dim LoadIntegersLoopCounter     As Long
    Dim ResultArrayColumnNumber     As Long
    Dim ResultArrayRow              As Long
    Dim NumberArray(MaxNumber * 2)  As Variant, ResultArray As Variant
'
    ReDim ResultArray(1 To MaxNumber, 1 To MaxNumber)                       ' Set Size of ResultArray ... Rows, Columns
'
    ArrayRow = 1                                                            ' Initialize ArrayRow to 1 ...
'
    For LoadIntegersLoopCounter = 1 To 2                                    ' Establish a loop to run 2 times
        For IntegerValue = 1 To MaxNumber                                   '   Establish a loop to run 1 to the maximum integer times
            NumberArray(ArrayRow) = IntegerValue                            '       Save the integer into NumberArray(ArrayRow)
            ArrayRow = ArrayRow + 1                                         '       Increment the NumberArray slot to have integers saved into
        Next                                                                '   Loop back for next integer
    Next                                                                    ' Loop back to repeat the process a 2nd time
'
'                                                                           ' NumberArray will now have a blank slot for NumberArray(0) & the range of integers
'                                                                           ' Two times in the rest of the NumberArray slots ie. If MaxNumber = 10 then ...
'                                                                           ' NumberArray(1) = 1, NumberArray(10) = 10, NumberArray(11) = 1, NumberArray(20) = 10
'
    For ResultArrayRow = 1 To MaxNumber                                     ' Establish first loop to save the rows for the 2D 1 Based ResultArray
        For ResultArrayColumnNumber = 1 To MaxNumber                        '   Establish 2nd loop to save the column values for the 2D 1 Based ResultArray
'
'           Save value from NumberArray to ResultArray
            ResultArray(ResultArrayRow, ResultArrayColumnNumber) = NumberArray(ResultArrayColumnNumber + ArrayOffset)
        Next                                                                '   Loop back for next column value to save
'
        ArrayOffset = NumberArray(ResultArrayColumnNumber - ResultArrayRow - 1) '   Adjust starting point of NumberArray values to be saved into ResultArray
    Next                                                                    ' Loop back for next row

    Range("O1").Resize(MaxNumber, MaxNumber) = ResultArray                  ' Display ResultArray to sheet
End Sub
Yes!! Excellent rewrite that great thank you so much as I know it takes time to do commenting let alone going back and making the variables more descriptive!!
 
Upvote 0
@zrx1200 You are welcome. Honestly, that last version is the way I normally post code that I do, I apologize for rushing the quick version through.
 
Upvote 0
That would be permutations? For combinations of 10 from a sample of 10, there is only one combination.
So, OP, what exactly is it that you're trying to achieve, without stating what you tried.
Revisit
So, from a math stand point I'm trying to understand this " For combinations of 10 from a sample of 10, there is only one combination."
In our solved vba code it gives me a 10*10, 100 cell range of what I call the combinations. This is more then 1 combination no?
I'm not a math wiz, so can someone help explain this some?

As Alex brought this up as well?

"unless there is a requirement to keep the numbers sequential, you know 10 number will give you 3,628,800 combinations right ?
(10*9*8*7*6*5*4*3*2*1)"

Sequential? what in the initial order from which your basing answer off of?
 
Upvote 0
Look up permutations vs combinations. They are different.
With 123 taken 3 at a time, there is only one combination. Plug this into a sheet - =COMBIN(3,3) - the answer is 1.
There are 6 permutations.
123
132
231
213
312
321
Oddly enough, a combination lock would be more correctly be called a permutation lock.
(10*9*8*7*6*5*4*3*2*1)"
That is not the formula for combinations. Suggest you Google it.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top