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
