You are here:

Excel/Help in Combinations



I need to make a excel plan with some combinations.
I have used a códe created by Tom Ogilvy but he is on vacation and i have some hurry to solve it.

see if you can help me.

I need to make all the possibles combinations with numbers from 1 to 10 with 6 combinations.

i have this code and its work very nice but i need that they repeat, like this:

1 2 3 4 5 6
1 1 1 1 1 6
1 2 2 3 4 4

all the possibles combinations, i'm not good in english but i'm trying to do the best explanation i can.

The Code:

Option Explicit

'C(n, p) = n! / ((n-p)! * p!)
'lPermutações a ser definido, seria o 'p' da fórmula acima
Const lPermutações As Long = 3

Dim r As Long

Dim v(1 To 5)

Sub Teste()
   Dim lElementos As Long
   'Popula vetor de elementos
   v(1) = "a"
   v(2) = "b"
   v(3) = "c"
   v(4) = "d"
   v(5) = "e"
   'C(n, p) = n! / ((n-p)! * p!)
   'lElementos seria o 'n' da fórmula acima
   lElementos = UBound(v) - LBound(v) + 1
   'Contador de linhas para uso no Excel:
   r = 0
   'Limpa Planilha ativa
   'Inicia recursão:
   Combinação lElementos, lPermutações, 1
End Sub

Sub Combinação(n As Long, p As Long, k As Long, Optional s As String)
   If p > n - k + 1 Then Exit Sub
   If p = 0 Then
       'Para visualizar o resultado de uma combinação no Excel:
       r = r + 1
       Cells(r, "A").Resize(1, lPermutações) = Split(s, "|")
       'Se quiser visualizar o resultado na Janela de Verificação imediata, use:
       Debug.Print s
       Exit Sub
   End If
   'Recorre novamente:
   Combinação n, p - 1, k + 1, s & v(k) & "|"
   'Recorre novamente a partir do elemento anterior:
   Combinação n, p, k + 1, s
End Sub

ANSWER: If I've understood the question, every number could be in any location, so it would go from 111111 to 101010101010 as the possible choices - if so, there are one million combinations - these can be generated with this code

Sub aidancode()
Dim a, b, c, d, e, f As Long
Dim outvar As Long
outvar = 2
For a = 1 To 10
   For b = 1 To 10
       For c = 1 To 10
         For d = 1 To 10
         For e = 1 To 10
         For f = 1 To 10
         Cells(outvar, 1).Value = a
         Cells(outvar, 2).Value = b
         Cells(outvar, 3).Value = c
         Cells(outvar, 4).Value = d
         Cells(outvar, 5).Value = e
         Cells(outvar, 6).Value = f
         outvar = outvar + 1
End Sub

---------- FOLLOW-UP ----------

QUESTION: Thanck you very much, it work!!!

But i have just more one question, i saw that in the macro you put "1 To 10" but how can i change this values for exemple:11.57, 11.78, 11.84, etc.

Not from X value to Y value, but 10 values that i want to put in the table.

I can chance in the macro "1 To 10" for "1 or 2 or 3... etc??"

If you want any 10 consecutive numbers you can simply amend the loop.  If as is more likely the numbers are not consecutive then this modified version would work for you.

Sub aidancode()
Dim a, b, c, d, e, f As Long
Dim outvar As Long
Dim Choices(10)
Choices(1) = 1
Choices(2) = 2
Choices(3) = 3
Choices(4) = 4
Choices(5) = 5
Choices(6) = 6
Choices(7) = 7
Choices(8) = 8
Choices(9) = 9
Choices(10) = 10
'change the above lines to have whatever 10 choices you want
outvar = 2

For a = 1 To 10
  For b = 1 To 10
      For c = 1 To 10
        For d = 1 To 10
         For e = 1 To 10
         For f = 1 To 10
         Cells(outvar, 1).Value = Choices(a)
         Cells(outvar, 2).Value = Choices(b)
         Cells(outvar, 3).Value = Choices(c)
         Cells(outvar, 4).Value = Choices(d)
         Cells(outvar, 5).Value = Choices(e)
         Cells(outvar, 6).Value = Choices(f)
         outvar = outvar + 1
End Sub

Just change the ten choices(x) lines to have the values you want to return.  Sorry for the slight delay in replying - I'm in the UK and was asleep when you asked the question!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 All rights reserved.