help with vba code

[Mati44]

I modified this vba code to generate combinations of six from 10 groups of pairs where only one number of a pair is used for each combination. but I can't get exactly what i want, and there many repetitions of the same combinations and some numbers are not even used in combinations. Can I get a little help to fix this code, please? thanks.

Here is the vba:

Sub Combs()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long
Dim g As Long
Dim h As Long
Dim j As Long
Dim k As Long
Dim i As Long
Dim grp
grp = Range("B1:C10").Value
Dim arr(1 To 1025, 1 To 10) As Long
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For j = 1 To 2
For k = 1 To 2
i = i + 1
arr(i, 1) = grp(1, a)
arr(i, 2) = grp(2, b)
arr(i, 3) = grp(3, c)
arr(i, 4) = grp(4, d)
arr(i, 5) = grp(5, e)
arr(i, 6) = grp(6, f)
arr(i, 7) = grp(7, g)
arr(i, 8) = grp(8, h)
arr(i, 9) = grp(9, j)
arr(i, 10) = grp(10, k)
Next k
Next j
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a
Application.ScreenUpdating = False
Range("H2").Resize(1025, 6).Value = arr
Application.ScreenUpdating = True
End Sub


Here is a sample data: https://1drv.ms/x/s!AoGkZUHlKui9gTjXXYF7IMAFfQW9?e=pqklc3

[SamT]

Lemmee guess: None of the numbers in Rows 7 to 10 show up.


This will probably still have some duplicates, and the odds that all numbers will be used is only 625 to 1

Make arr larger , then Use Excel to filter for duplicates until you have 1025 unique values, and/or use all 20 numbers


Option Explicit

Sub Combs()
Dim j As Integer
Dim i As Long
Dim Rw As Integer
Dim Col As Integer
Dim grp As Variant

grp = Range("B1:C10").Value
Dim arr(1 To 1025, 1 To 6) As Long

Col = 1
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
Randomize
If Col = 1 Then
Col = 2
Else
Col = 1
End If

arr(i, j) = grp(CInt(10 * (Rnd + 1)), Col)
Next j: Next i

Range("H2").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

[Mati44]

Thanks for your answer. yes your assumption is correct.
I tried your code, but I got an error.

Subscript out of range error on this line :
arr(i, j) = grp(CInt(10 * (Rnd + 1)), Col)

[SamT]

What were the values on i, j, Col, and CInt(10 * (Rnd + 1))?

Try editing "CInt" to "CLng"

[Mati44]

I still get the same error. Have you tried this code?

[mohadin]

Hi

arr(i, j) = grp(Int((10 - 1 + 1) * Rnd + 1), Col)

[euleriscool]

Thanks also helped.

[Mati44]

Thanks, I did the change, the code works, but it doesn't give the correct answers. The rule is that only one number should be picked from any group to make the combination, and the list I created contains even repetitions.

[SamT]

It takes a lot of code, or a little Excel Filtering, to prevent, or delete, duplicates

[Mati44]

thanks, SamT.