How to create a range number generator. 1

[kuda25]

Dear experts.

I am trying to create a number generator over a range of cells and being new to vba the only way I can think of doing it is using loops and refering to each cell. Hopefully you can help. Here is the detail:

Create a table in excel of dimensions x and y, with x and y being variable.
Create a macro that can vary the each cell value in the table between range 0 to n
and run through all possible combinations of values in the table.
the ranges for each column maybe different.

Any ideas? As mentioned, my initial thought is to do this using loops and refering to each cell. I don't yet know how to do this for a range.

Kuda

 

[Gavona]

Permutations or combinations? Either way the task is huge.

http://www.google.co.uk/search?q=ex...s=org.mozilla:en-US:official&client=firefox-a

http://www.mrexcel.com/forum/showthread.php?t=304538

Would creating random numbers suffice? If so the formula would be something like:
=Rand()*(maxValue-minValue) + minValue

http://www.mathwave.com/articles/random-numbers-excel-worksheets.html

Returning to your question, one way would be:

Dim c as Cell
For each c in myRange
  'do something
Next c

But you can also loop using the Cells property to refer to a single cell by using row and column index numbers.
In VBA help look up "Referring to Cells by Using Index Numbers"


Gavin

 

[electricpete]

Is it a homework question?

 

[kuda25]

No, this isn't a homework question. It's part of a larger model that i am trying to build for work. any help would be appreciated

 

[kuda25]

This involves some combinations. Maybe I will try explain this in a different.

Let imagine that each cell is a person holding a certain number of objects, say oranges. that person can hold between 0 and 10 oranges. the table is a collect of these people, classified in someway. eg nationality along the columns and age along the rows.

The idea is that the individual cells are independent and the value in the cell can range from 0 to 10.
The macro should generate all possible combination of cell values for the range.

So for example with a range of 2 cells the possible combinations would include:
0/0 ,1/0, 0/1, 1/1 , 1/2,.... all the way to 10/10.

Hope what I am trying to do makes more sense now.

Kuda

 

[kuda25]

Seem to finding this hard to explain. Let me try this again.

I am trying to create a macro that produces all possible tables, given:
- the table dimensions - dimensions are used defined.
- the cell values can range from 0 to 10, independent of location

So for a table for a 2 x3 dimension range. the different tables the macro would produce would include

000 100 010 001 .......... 555 ......... 101010
000 000 000 000 555 101010

So the macro is producing all the possible tables based on the 0 to 10 range of values for each cell. For table range of dimensions 3by2 this should give 11^(3*2) possible tables.

 

[Gavona]

If 1/0 and 0/1 are seen as different then you are talking about permutations not combinations.

(I don't follow your exemplification. I can imagine a table where in a particular cell you have the number of people of the given nationality and age.
Or one where the cell represents the total number of oranges held by all the people of that age and nationality. I find it odd that the number of people would not be needed and act as a constraint to the results set.)

Of course, as you describe the task the permutations for each row are identical.
In your 2x1 example each cell can have 11 values so there are 11 x 11 possible results = 121

If you had a 2x2 table then the number of results would be 121 x 121 = 14,641
If it was 2x3 then the number of results would be 14641x121 results each of which would be a 2x3 table so more than 5m rows of output.

How do you want the results output and what further processing are you intending?

Gavin

 

[kuda25]

Gavona,

I think the example i gave before is a bit confusing. But I think you are starting to get the idea.

to clarify:

I am trying to create a macro that produces all possible tables, given:
- the table dimensions - dimensions are used defined.
- the cell values can range from 0 to 10, independent of location

So for a table for a 2 x3 dimension range. the different tables the macro would produce would include

000 100 010 001 .......... 555 ......... 101010
000 000 000 000 ...........555 ......... 101010

So the macro is producing all the possible tables based on the 0 to 10 range of values for each cell. For table range of dimensions 3by2 this should give 11^(3*2) possible tables.

With one of 2 cells I would normally loop through all possible combinations but the variable table dimensions have me stuck on how to proceed. any ideas?

In terms of outputting this, The outputs will be used in a wider calculation and so will be placed in the same range in cell.

 

[Gavona]

It will take 'forever' to process the results and therefore to get through all the possible combinations. Because you will approach the task in a structured way the results will be biased / not representative until you have gone through all combinations.
So are you sure that you can't just go for random number formulae? A statistician could probably even tell you how many iterations (samples) you would need in order to be confident that your results are reflective of the whole population.

Sorry, I don't think I have the expertise to suggest how to generate the results beyond reviewing those links I suggested.

Gavin

 

[Gavona]

The total number of combinations you have is:
(MaxValue^NoColumns)^NoRows
or
MaxValue^(NoColumns*NoRows)

This non-VBA partial solution inspired by

http://www.excelforum.com/excel-worksheet-functions/588901-permutation-and-combinations.html

would seem to help. I have put all the results for the table into a single column rather than splitting by row and column. (Replace Resultset with column() and copy the formulae to the right to see all the different result sets side by side):

The named range MaxValue is equal to 1 plus the maximum value you are specifying.
The Named range Resultset contains a number from 1 to MaxValue^(NoColumns*NoRows) (you could increment this by code and merely adapt the formulae I show here to fit your table layout).

in A1 put
=MOD(Resultset-1,MaxValue)

in A2 and all further rows down to (NoColumns*NoRows)
=MOD(TRUNC((Resultset-1)/(MaxValue^(ROW()-1))),MaxValue)


This is interesting academically but my suggestion re randomly generated result sets to give a statistically reliable representation of the population still holds.

Good luck!


Gavin

 

[MajP]

This is not as difficult as Gavona alludes. I believe this code is correct, but please do some testing. However, this can take a long time. Your example of R = 3x2 = 6, and N (0-10) = 11 took a very long time. However, no issue with an R of 5, or 161051 possibilities.

According to your example you are describing permutations with replacement. If you were dealing with combinations or without replacement the problem is orders of magnitude harder.

N things choose R at a time with replacement (order matters and you can use each value more than once)
N-c-R: Possibilities is N^R.

This is how I would do it. I would figure out all the possibilities and save in a 2D array. Then read through the array to make the tablels.

So a simple example would be 1x2 table. That is R of 2. A 3X2 table is an R of (6). And values 1-4 or an N of 4.

so the choices look like for 4 C 2

1 1
1 2
1 3
1 4
2 1
2 2
...
4 4

notice that the first column changes value every 4th record, and the second choice changes every value. If you expanded it 4 C 3 your choices would be

1 1 1
1 1 2
1 1 3
1 1 4
1 2 1
1 2 2
1 2 3
1 2 4

Assume the above has column 1,2,3. Lets call that J.
If you did all the possibilites then first column changes its value every
N^R / n^1 or 64/4 = 16
the second
N^r/N^2, 64/16 = 4
the last
N^r/N^3 64/64 = 1

So Fill the array in this manner changing the value for that J based on the repeat value.

Once the array is filled you than can fill your tables. This example just prints all the possible tables.

Public Sub TestNChooseR()
  'N = 11 (0,1,2,3,4,..10)
  'R = 5
 Print2DArray (NChooseRWithReplacement(5, 0, 10))
 'put each row into a table. for you to code
End Sub

Public Function NChooseRWithReplacement(R As Integer, LowerNumber As Integer, upperNumber As Integer) As Variant
  Dim N As Integer
  Dim i As Integer
  Dim permutations() As Variant
  Dim ElementsOfN() As Variant
  N = Abs(upperNumber - LowerNumber) + 1
  'Debug.Print N
  If (upperNumber > LowerNumber) And N >= R Then
    ReDim ElementsOfN(N)
    ReDim permutations((N ^ R) - 1, R - 1)
    For i = LBound(ElementsOfN) To UBound(ElementsOfN)
      ElementsOfN(i) = LowerNumber + i
    Next i
   'Print1DArray (ElementsOfN)
    NChooseRWithReplacement = PermuteNChooseRWithReplacement(N, R, ElementsOfN, permutations)
  End If
End Function

Public Function PermuteNChooseRWithReplacement(ByVal N As Integer, ByVal R As Integer, ElementsOfN As Variant, permutations As Variant) As Variant
  Dim NToTheR As Long
  Dim IPermutations As Long
  Dim IElementsOfN As Integer
  Dim J As Integer
  Dim repeatvalue As Long
  NToTheR = N ^ R
  MsgBox "N^R " & NToTheR
  For J = LBound(permutations, 2) To UBound(permutations, 2)
     repeatvalue = NToTheR / (N ^ (J + 1))
     'MsgBox "J " & J
     'MsgBox "repeat value " & repeatvalue
     For IPermutations = LBound(permutations, 1) To UBound(permutations, 1)
       IElementsOfN = groupIndex(IPermutations + 1, repeatvalue, N)
         permutations(IPermutations, J) = ElementsOfN(IElementsOfN)
     Next IPermutations
  Next J
  'MsgBox UBound(permutations, 1)
  PermuteNChooseRWithReplacement = permutations
End Function

Public Function groupIndex(ByVal iteration As Long, ByVal repeatvalue As Long, items As Integer) As Long
  'assume 3 items, you repeat each item 4 times
  'Then on the 5 iteration you would pull the second item
  'Like arrays the group is zero indexed
  'First call is one not zero even if the index is 0
  iteration = iteration - 1
  If iteration >= items * repeatvalue Then
    Do Until iteration < items * repeatvalue
       iteration = iteration - (items * repeatvalue)
    Loop
  End If
  groupIndex = Fix(iteration / repeatvalue)
End Function

Public Sub Print1DArray(aVar As Variant)
  Dim i As Long
  Dim J As Long
  For i = LBound(aVar) To UBound(aVar)
     Debug.Print aVar(i)
  Next i
  
End Sub
Public Sub Print2DArray(aVar As Variant)
  Dim i As Long
  Dim J As Long
  For i = LBound(aVar, 1) To UBound(aVar, 1)
    For J = LBound(aVar, 2) To UBound(aVar, 2)
      Debug.Print aVar(i, J)
    Next J
    Debug.Print
  Next i
End Sub