sasha09

New Member
Anybody, please help me. I am not good with VBA macros but I need to do a complete enumeration of number permutations

Here's my problem.

I have 8 objects--1, 2, 3, 4, 5, 6, 7 and 8. Each "solution" must contain all 8 objects, but the order matters. I need a macro that can generate all of these possible permutations.

Additionally, each of the objects need to be placed in a separate cell. It should look like this, where each row is a "solution:

 1st Object 2nd Object 3rd Object 4th Object 5th Object 6th Object 7th Object 8th Object 1 2 3 4 5 6 7 8 1 2 3 4 5 6 8 7 1 2 3 4 5 7 6 8 1 2 3 4 5 7 8 6

<tbody>
</tbody>

And that list would continue until:

 8 7 6 5 4 3 2 1

<tbody>
</tbody>

Please help me. I am so clueless as to what I need to do. Thank you to all the kind souls out there.)

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try this where you will enter 12345678 in A1, then rub Sub DoString().
Then run sub Sub TxToCoL().
The end data will start in cell D1 over eight columns and down 40,320 rows. (Change D1 to suit where you want the data to go.)
If you use other than exactly eight digits, you may want to un comment the first line of code in TxToCol and comment out the second.

Regards,
Howard

Code:
``````Option Explicit
Option Compare Text
Dim CurrentRow

Sub DoString()
On Error Resume Next
Dim Instring As String
Dim i As Integer, j As Integer
Instring = Range("A1").Value
Range("A1").Select
CurrentRow = 1
Call GetPermutation("", Instring)

End Sub
Sub GetPermutation(X As String, y As String)
On Error Resume Next
Dim j, i
j = Len(y)
If j < 2 Then
Cells(CurrentRow, 1) = X & y
CurrentRow = CurrentRow + 1
Else
For i = 1 To j
Call GetPermutation(X + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next
End If
End Sub

Sub TxToCoL()
'Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select
Range("A1:A40320").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _
(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), _
TrailingMinusNumbers:=True
End Sub``````

A slight rewrite to make it a one step click and done.

Just run Sub DoString().

Regards,
Howard

Code:
``````Option Explicit
Option Compare Text
Dim CurrentRow
Sub DoString()
On Error Resume Next
Dim Instring As String
Dim i As Integer, j As Integer
Instring = Range("A1").Value
Range("A1").Select
CurrentRow = 1
Call GetPermutation("", Instring)
TxToCoL
End Sub

Sub GetPermutation(X As String, y As String)
On Error Resume Next
Dim j, i
j = Len(y)
If j < 2 Then
Cells(CurrentRow, 1) = X & y
CurrentRow = CurrentRow + 1
Else
For i = 1 To j
Call GetPermutation(X + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next
End If
End Sub
Sub TxToCoL()
'Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select
Range("A1:A40320").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _
(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), _
TrailingMinusNumbers:=True
End Sub``````

This did the trick! Thank you sooo much!!! :D :D :D

You are welcome, thanks for the feed back.

Regards,
Howard

Anybody, please help me. I need to do a complete enumeration of number permutation for sports betting accumulation.

Here's my problem

There a 5 matches being played in the NBA

LA Lakers(LAL) Vs Sacramento Kings(SAC)
Toronto Raptors(TOR) Vs Atlanta Hawks(ATL)
LA Clippers(LAC) Vs San Antonio Spurs(SAS)
Memphis Grizzlies(MEM) Vs Phoenix Suns(PHX)
Portland Trail Blazers(POR) Vs Cleveland Cavaliers(CLE)

Let's assume the the result for ;

First match LA Lakers Vs Sacramento King is

(100 - 700) the sum is 170 which is an EVEN number
OR
(107 - 98) the sum is 205 which is an ODD number

Second match Toronto Raptors Vs Atlanta Hawks is

(112- 117) the sum is 229 which is an ODD number
OR
(98 - 102) the sum is 200 which is an EVEN number

Third match LA Clippers Vs San Antonio Spurs is

(70 - 72) the sum is 142 which is an EVEN number
OR
(100- 79) the sum is 179 which is an ODD number

Fourth Match Memphis Grizzlies Vs Phoenix Suns is

(105 - 116) the sum is 221 which is an ODD number
OR
(98 - 90) the sum is 188 which is an EVEN number

Fifth Match Portland Trail Blazers Vs Cleveland Cavaliers is

(98 -86) the sum is 184 which is an EVEN number
OR
(85 - 110) the sum is 195 which is an ODD number

*For every match there are two possible outcomes either ODD or EVEN
i need a macro to generate all the possible ARRANGEMENT(permutation) without repetition, of the outcome either ODD or EVEN of all the 5 matches to be placed on a betting slip

Here's an illustration

1st Betting Slip 2nd Betting Slip 3rd Betting Slip 4th Betting Slip 5th Betting Slip 6th Betting Slip
LAL Vs SAC - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - EVEN
TOR Vs ATL - ODD TOR Vs ATL - EVEN TOR Vs ATL - EVEN TOR Vs ATL - ODD TOR Vs ATL - ODD TOR Vs ATL - EVEN
LAC Vs SAS - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC- EVEN
MEM Vs PHX - ODD LAL Vs SAC - EVEN LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - ODD LAL Vs SAC - EVEN
POR Vs CLE - ODD POR Vs CLE- EVEN POR Vs CLE - ODD POR Vs CLE - ODD POR Vs CLE - EVEN POR Vs CLE- ODD

Please i need a full permutation(arrangement) of outcome of the matches without repetition.

Replies
5
Views
121
Replies
18
Views
394
Replies
1
Views
151
Replies
0
Views
384
Replies
4
Views
682

1,196,057
Messages
6,013,165
Members
441,751
Latest member
336448

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.

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

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