You are here:

# Excel/Combination between columns in a area

Question
Mr.

I want to combine vector or range instead string read in v(i). The v(i) will be columns in some data area. Can you help me, please?!
Thanks a lot (pardon for my english)
Ricardo

********************************

Option Explicit

Dim lPermutacoes As Long
Dim lElementos As Long
Dim r As Long
Dim v()
Dim i As Integer
Dim k As Integer
Dim j As Integer

Sub Teste()
'C(n, p) = n! / ((n-p)! * p!)
'lPermutacoes a ser definido, sendo o 'p' da fórmula acima

j = 0
k = Range("M1").CurrentRegion.Columns.Count

ReDim v(1 To k) ' as Long)

'Populacao vetor de elementos
v(1) = Range("k1")
v(2) = Range("L1")
v(3) = Range("M1")
v(4) = Range("N1")
v(5) = Range("O1")
v(6) = Range("P1")
v(7) = Range("Q1")

For i = 1 To k

lPermutacoes = i

lElementos = UBound(v) - LBound(v) + 1

'Contador de linhas para uso no Excel:
r = 0

'Limpa Planilha ativa
Columns("A:i").ClearContents
'Cells.Delete

'Inicia recursão:
Combination lElementos, lPermutacoes, 1

'conta o numero de combinacoes
j = Range("A1600").End(xlUp).Row + j
Next i
Stop
Columns("A:i").ClearContents

End Sub

Sub Combination(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, lPermutacoes) = Split(s, "|")

'Se quiser visualizar o resultado na Janela de Verificação imediata, use:
Debug.Print s

Exit Sub
End If
'Recorre novamente:
Combination n, p - 1, k + 1, s & v(k) & "|" 'original

'Recorre novamente a partir do elemento anterior:
Combination n, p, k + 1, s
End Sub

ricardo miguel de souza,

I am not sure what you are asking.  I will assume you want to specify M1 as the upper left corner of a range and then you want the array v to pick up the values from the top row of that range.

Option Explicit

Dim lPermutacoes As Long
Dim lElementos As Long
Dim r As Long
Dim v()
Dim i As Integer
Dim k As Integer
Dim j As Integer

Sub Teste()
'C(n, p) = n! / ((n-p)! * p!)
'lPermutacoes a ser definido, sendo o 'p' da fórmula acima

j = 0
k = Range("M1").CurrentRegion.Columns.Count

ReDim v(1 To k) ' as Long

'Populacao vetor de elementos
For i = 1 to k
v(i) = Range("M1").offset(0, i - 1).value
Next

For i = 1 To k

lPermutacoes = i

lElementos = UBound(v) - LBound(v) + 1

'Contador de linhas para uso no Excel:
r = 0

'Limpa Planilha ativa
Columns("A:i").ClearContents
'Cells.Delete

'Inicia recursão:
Combination lElementos, lPermutacoes, 1

'conta o numero de combinacoes
j = Range("A1600").End(xlUp).Row + j
Next i
Stop
Columns("A:i").ClearContents

End Sub

Sub Combination(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, lPermutacoes) = Split(s, "|")

'Se quiser visualizar o resultado na Janela de Verificação imediata, use:
Debug.Print s

Exit Sub
End If
'Recorre novamente:
Combination n, p - 1, k + 1, s & v(k) & "|" 'original

'Recorre novamente a partir do elemento anterior:
Combination n, p, k + 1, s
End Sub

If that isn't what you were describing, then sorry - I don't understand what you want.

--
Regards,
Tom Ogilvy

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

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.