You are here:

Excel/Combination between columns in a area

Advertisement


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

Answer
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

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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


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.

©2016 About.com. All rights reserved.