Say I have 5 numbers located in A2, C2, E2, G2, I2.
How to calculate the summation of each 3 random product exhaustively?
That is
A2*C2*E2 +
A2*C2*G2 +
A2*C2*I2 +
..... +
E2*G2*I2
=result.

What if 5 numbers to be N numbers and 3 multiplications to be M times?

Thanks!

See if this works. I put the results into a worksheet so you can see the
results.

Sub ProcessRandomNumbers()

Dim Combo()
Dim InputData()
Dim Data()

'dimension random number array as 2 dimensional
ReDim InputData(0 To 1, 0 To 0)

Randomize ' Initialize random-number generator.


Set mycell = Application.InputBox( _
prompt:="Select Random Numbers", Type:=8)

Count = 0
For Each cell In mycell
If IsNumeric(cell) Then
ReDim Preserve InputData(0 To 1, 0 To Count)
InputData(0, Count) = cell
'create random number for each input
InputData(1, Count) = Rnd()
Count = Count + 1
End If
Next cell

'sort array by random number to get randomize numbers
For i = 0 To (UBound(InputData, 2) - 1)
For J = (i + 1) To (UBound(InputData, 2))
If InputData(1, i) InputData(1, J) Then
'switch number to sort
Temp = InputData(1, i)
InputData(1, i) = InputData(1, J)
InputData(1, J) = Temp

Temp = InputData(0, i)
InputData(0, i) = InputData(0, J)
InputData(0, J) = Temp
End If
Next J
Next i

DataLen = UBound(InputData, 2) + 1

Do
RandSize = Val(InputBox("Enter Number of Random Numbers from 1 to " &
DataLen))

Loop While RandSize <= 0 And Size DataLen


Do
Size = Val(InputBox("Enter Size from 1 to " & RandSize))

Loop While Size <= 0 And Size RandSize



ReDim Combo(Size)
ReDim Data(0 To (RandSize - 1))
'Put number of random values into array
For i = 0 To (RandSize - 1)
Data(i) = InputData(0, i)
Next i


'put results in sheet "Results"
'check if sheet exists
Found = False
For Each Sht In Sheets
If UCase(Sht.Name) = "RESULTS" Then
Found = True
Exit For
End If
Next Sht
If Found = False Then
Set Resultsht = Sheets.Add(after:=Sheets(Sheets.Count))
Resultsht.Name = "Results"
Else
Set Resultsht = Sheets("Results")
End If

Level = 1
RowCount = 1
Resultsht.Cells.ClearContents
Call Recursive(Resultsht, Data, Combo(), Level, Size, RowCount)

With Resultsht
.Range("A" & RowCount) = "Total"
.Cells(RowCount, Size + 1).FormulaR1C1 = _
"=sum(R1C" & (Size + 1) & ":R" & _
(RowCount - 1) & "C" & (Size + 1) & ")"
End With
End Sub
Sub Recursive(Resultsht, Data, Combo, Level, Size, RowCount)

DataLen = UBound(Data) + 1
'make combination
For Count = (Combo(Level - 1) + 1) To _
DataLen - (Size - Level)

Combo(Level) = Count
If Level = Size Then
For ColCount = 1 To Size
Resultsht.Cells(RowCount, ColCount) = _
Data(Combo(ColCount) - 1)
Next ColCount
Resultsht.Cells(RowCount, Size + 1).FormulaR1C1 = _
"=product(R" & RowCount & "C1:R" & _
RowCount & "C" & Size & ")"

RowCount = RowCount + 1
Else
Call Recursive(Resultsht, Data, Combo, Level + 1, Size, RowCount)
End If
Next Count
End Sub


"Boku" wrote:

Say I have 5 numbers located in A2, C2, E2, G2, I2.
How to calculate the summation of each 3 random product exhaustively?
That is
A2*C2*E2 +
A2*C2*G2 +
A2*C2*I2 +
.... +
E2*G2*I2
=result.

What if 5 numbers to be N numbers and 3 multiplications to be M times?

Thanks!