Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to run combinations

  Asked By: Rabiah    Date: Dec 16    Category: MS Office    Views: 603
  

I'm trying to run all possible combinations in a variable range, but
cannot seem to get my code to work...

The range can be from 1 to 6 cells, each with the same minimum and
maximum values (such as -100 to 100).

Either of following will try the individual cells of range "R" from
-10 to 10, but not all possible combinations (ie; Given a selection
range of 2 cells, run -10 in the first cell, then -10 to 10 in the
second, then -9 and -10 to 10, etc).

Sub MyLoopEachAll(R)
For Each cell In R
For x = -10 To 10 'Min1.Value To Max1.Value
cell.Value = x
Next x
Next cell
End Sub

Sub MyLoopForAll(R)
For x = 1 To R.Count
For y = -10 To 10 'Min1.Value To Max1.Value
R(x).Value = y
Next y
Next x
End Sub


Hope I've made the problem clear...

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Anthony Smith     Answered On: Dec 16

Sounds like you need recursion! Basically you need a function (or
subroutine) which will loop through each of the possible values  for a
given cell, but each time it changes the value in that cell, it calls
itself for the next cell. Within that function, you need some way to
test for completion, i.e. when it gets to the last cell. The code
below will run  your combinations  in the cells  of row 1:

Const MIN = -10
Const MAX = 10

Sub RunCombinations()
RunCellValues 5, 1 ' change first value to your liking
End Sub

Sub RunCellValues(ByVal iCount As Integer, ByVal iIndex As Integer)

Dim ivalue As Integer

If iIndex <= iCount Then
For ivalue = MIN To MAX
Cells(1, iIndex).Value = ivalue
RunCellValues iCount, iIndex + 1
Next ivalue
End If

End Sub

 
Answer #2    Answered By: Edna West     Answered On: Dec 16

I'll play with that a bit tomorrow and see if I can make it
sit up and do tricks...

 
Answer #3    Answered By: Guilherme Silva     Answered On: Dec 16

Got it working, but seem to be getting some duplicate
results...

Would this code run  the same test twice?

 
Didn't find what you were looking for? Find more on How to run combinations Or get search suggestion and latest updates.




Tagged:    

 
 
 
 

Related Post