array/for next loops

[zrx1200]

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
	O	P	Q	R	S	T	U	V	W	X
1	1	2	3	4	5	6	7	8	9	10
2	10	1	2	3	4	5	6	7	8	9
3	9	10	1	2	3	4	5	6	7	8
4	8	9	10	1	2	3	4	5	6	7
5	7	8	9	10	1	2	3	4	5	6
6	6	7	8	9	10	1	2	3	4	5
7	5	6	7	8	9	10	1	2	3	4
8	4	5	6	7	8	9	10	1	2	3
9	3	4	5	6	7	8	9	10	1	2
10	2	3	4	5	6	7	8	9	10	1
11	2	3	4	5	6	7	8	9	10	1
12	1	2	3	4	5	6	7	8	9	10
13	10	1	2	3	4	5	6	7	8	9
14	9	10	1	2	3	4	5	6	7	8
15	8	9	10	1	2	3	4	5	6	7
16	7	8	9	10	1	2	3	4	5	6
17	6	7	8	9	10	1	2	3	4	5
18	5	6	7	8	9	10	1	2	3	4
19	4	5	6	7	8	9	10	1	2	3
20	3	4	5	6	7	8	9	10	1	2
21	3	4	5	6	7	8	9	10	1	2
22	2	3	4	5	6	7	8	9	10	1
23	1	2	3	4	5	6	7	8	9	10
24	10	1	2	3	4	5	6	7	8	9
25	9	10	1	2	3	4	5	6	7	8
26	8	9	10	1	2	3	4	5	6	7
27	7	8	9	10	1	2	3	4	5	6
28	6	7	8	9	10	1	2	3	4	5
29	5	6	7	8	9	10	1	2	3	4
30	4	5	6	7	8	9	10	1	2	3
old1172022

I have it doing this fig 2 so far.
fig2.

Paired Matches.xlsm
	O	P	Q	R	S	T	U	V	W	X
33	1	2	3	4	5	6	7	8	9	10
34	1	2	3	4	5	6	7	8	9	10
35	1	2	3	4	5	6	7	8	9	10
36	1	2	3	4	5	6	7	8	9	10
37	1	2	3	4	5	6	7	8	9	10
38	1	2	3	4	5	6	7	8	9	10
39	1	2	3	4	5	6	7	8	9	10
40	1	2	3	4	5	6	7	8	9	10
41	1	2	3	4	5	6	7	8	9	10
42	1	2	3	4	5	6	7	8	9	10
old1172022

 

[Micron]

Are you starting in row 2 or row 1, and why do rows 11 and 21 repeat?
I thought I was on to something, but those 2 rows threw me a curve ball (and I think I struck out).

 

[zrx1200]

Public Sub Learn_Arrays_loops()

  Dim i As Long, rng As Range, startcell As Range
  Dim strNames() As String
 

   ReDim strNames(1 To 10)
'populate the array
   strNames(1) = "1"
   strNames(2) = "2"
   strNames(3) = "3"
   strNames(4) = "4"
   strNames(5) = "5"
   strNames(6) = "6"
   strNames(7) = "7"
   strNames(8) = "8"
   strNames(9) = "9"
   strNames(10) = "10"

  Do
     Set rng = ActiveCell
     Set startcell = ActiveCell
 
  Count = Count + 1
 
         For i = 1 To 10
          rng.Offset(0, i - 1).Value = strNames(i)
         Next i

      startcell.Offset(1, 0).Select
     i = 0
  Loop Until Count = 10  ' count for testing purposes to stop looping (to be replaced with a met condition once I figure that out)

End Sub

Are you starting in row 2 or row 1, and why do rows 11 and 21 repeat?
I thought I was on to something, but those 2 rows threw me a curve ball (and I think I struck out).

ok good eye I never picked up on the 11,21 rows. Hmm interesting without this how would I start my shift of numbers I'm wondering... things that make one go hmmm... pondering.



So, yes started in row one 1 to 10 then row two shift all to right and 10 comes in A2 then 1 to 9 fill in behind and so on.

However, I may need to try and explain better. The rows are a complete (what??? combination I guess), so each number can be in any cell as I'm trying to show in fig1 thus all possible combination of where numbers can be in cells or column positions may be a better statement??

 

[zrx1200]

possible revised "list with the dups of row 11, 21.

Paired Matches.xlsm
	O	P	Q	R	S	T	U	V	W	X
1	1	2	3	4	5	6	7	8	9	10
2	10	1	2	3	4	5	6	7	8	9
3	9	10	1	2	3	4	5	6	7	8
4	8	9	10	1	2	3	4	5	6	7
5	7	8	9	10	1	2	3	4	5	6
6	6	7	8	9	10	1	2	3	4	5
7	5	6	7	8	9	10	1	2	3	4
8	4	5	6	7	8	9	10	1	2	3
9	3	4	5	6	7	8	9	10	1	2
10	2	3	4	5	6	7	8	9	10	1
11	1	2	3	4	5	6	7	8	9	10
12	10	1	2	3	4	5	6	7	8	9
13	9	10	1	2	3	4	5	6	7	8
14	8	9	10	1	2	3	4	5	6	7
15	7	8	9	10	1	2	3	4	5	6
16	6	7	8	9	10	1	2	3	4	5
17	5	6	7	8	9	10	1	2	3	4
18	4	5	6	7	8	9	10	1	2	3
19	3	4	5	6	7	8	9	10	1	2
20	2	3	4	5	6	7	8	9	10	1
21	1	2	3	4	5	6	7	8	9	10
22	10	1	2	3	4	5	6	7	8	9
23	9	10	1	2	3	4	5	6	7	8
24	8	9	10	1	2	3	4	5	6	7
25	7	8	9	10	1	2	3	4	5	6
26	6	7	8	9	10	1	2	3	4	5
27	5	6	7	8	9	10	1	2	3	4
28	4	5	6	7	8	9	10	1	2	3
old1172022

So, it looks like doing manually, one could keep the shift I have been describing would still work out?

So, does this help in a macro solution....

 

[zrx1200]

duh..wait a minute post #4 those number are just repeats...

Now I'm getting confused!!!!

All, I wish to do is get a macro to lay down the numbers in every possible combination within the cells of those 10 columns.

 

[Alex Blakenburg]

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)

 

[Micron]

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)

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.

 

[zrx1200]

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.

So, after looking at this again and Micron you picked up on this with your 11,21 rows being dupes, is the answer has always been there but I was making it more difficult!
I also was, (I believe) presenting it wrong possibly.
The combinations I was looking for was full row which includes all 10 numbers to make one combination. Thus 10 numbers*10 columns = 100 cells
So below is the answer as it I believe has all combos.

Sorry for confusion, does this now make sense to us lol...

Paired Matches.xlsm
	O	P	Q	R	S	T	U	V	W	X
1	1	2	3	4	5	6	7	8	9	10
2	10	1	2	3	4	5	6	7	8	9
3	9	10	1	2	3	4	5	6	7	8
4	8	9	10	1	2	3	4	5	6	7
5	7	8	9	10	1	2	3	4	5	6
6	6	7	8	9	10	1	2	3	4	5
7	5	6	7	8	9	10	1	2	3	4
8	4	5	6	7	8	9	10	1	2	3
9	3	4	5	6	7	8	9	10	1	2
10	2	3	4	5	6	7	8	9	10	1
old1172022

 

[Micron]

Can we see your final code? I don't seem to have enough to do over at AccessForums.net so I'm dabbling in Excel vba now.
BTW, if you care, you have 10 permutations, not 10 combinations.

 

[zrx1200]

Can we see your final code? I don't seem to have enough to do over at AccessForums.net so I'm dabbling in Excel vba now.
BTW, if you care, you have 10 permutations, not 10 combinations.

Ok, proper terms, permutations good to know, yes I do care LOL thank you for sharing.

Code I've started is at post #3

desired results post #8

So, each row from 1 through 10 and column 1 through 10 will have all the numbers from range(A1:J1) in its ROW range someplace. Again post 8 shows desired results done by a macro we create if possible.