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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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).
 
Upvote 0
VBA Code:
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??
 
Upvote 0
possible revised "list with the dups of row 11, 21.

Paired Matches.xlsm
OPQRSTUVWX
112345678910
210123456789
391012345678
489101234567
578910123456
667891012345
756789101234
845678910123
934567891012
1023456789101
1112345678910
1210123456789
1391012345678
1489101234567
1578910123456
1667891012345
1756789101234
1845678910123
1934567891012
2023456789101
2112345678910
2210123456789
2391012345678
2489101234567
2578910123456
2667891012345
2756789101234
2845678910123
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....
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
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.
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
OPQRSTUVWX
112345678910
210123456789
391012345678
489101234567
578910123456
667891012345
756789101234
845678910123
934567891012
1023456789101
old1172022
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,202,958
Messages
6,052,785
Members
444,600
Latest member
margr

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