Random Variables

[anca.taciuc]

Hi all,

I am writing you because I am a bit stuck with the module that I want to create in VBA.

Basically, I have two sheets. In one of them called "Intervals" I have several columns having different no of cells as seen below:



L
T
A
V


200
2
12
0.2


100
6
10
0.5


50

5
0.8


60


1.0



My intention is to create another sheet called "Input", having n number of combinations of the values from the 4 columns.
n will be giving as an input by the user via an inputbox.

Something like:

n = 4




200
2
10
0.8


50
2
5
0.2


50
6
12
0.2


60
2
5
1.0



This is what I have done so far. If someone could help it would be great.

Thank you.


Sub Parameters_Input()
Dim mybook As Workbook, BaseWks As Worksheet, s2 As Worksheet
Dim myValue As Variant
Dim Cnum As Long, noCell As Long
Dim n As Double
Dim R2 As Range


Application.DisplayAlerts = False

'detele
For q = 1 To Worksheets.Count
If Worksheets(q).Name = "Input" Then
Worksheets(q).Delete
Exit For
End If
Next q

Application.DisplayAlerts = True

' Add a new workbook with one sheet.
Set mybook = ThisWorkbook
Set BaseWks = mybook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
BaseWks.Name = "Input"

myValue = InputBox("Please enter the number of simulations")

Worksheets("Intervals").Activate

Set s2 = Sheets("Input")

Cnum = Range("AZ1").End(xlToLeft).Column

For i = 1 To Cnum
noCell = Cells(Rows.Count, i).End(xlUp).Row
Set R2 = s1.Range(Cells(1, 1), Cells(Cnum, myValue))
.....
n = Application.WorksheetFunction.RandBetween(Cells(2, i), Cells(noCell, i))
....
Next i

End Sub

[mancubus]

welcome to vbax.


pick 4 columns or Cnum columns?



Sub vbax_54454_Random_Cells_Values()

Dim SimNum As Long, LastCol As Long

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Input").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Input"

SimNum = InputBox("Please enter the number of simulations")

With Worksheets("Intervals")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 1 To SimNum
For j = 1 To LastCol 'For j = 1 To 4 '?
RandRowNo = WorksheetFunction.RandBetween(2, .Cells(.Rows.Count, j).End(xlUp).Row)
Worksheets("Input").Cells(i, j).Value = .Cells(RandRowNo, j).Value
Next j
Next i
End With

End Sub

[JeffK]

g

[JeffK]

Excuse the gibberish - ran into a browser glitch.
JeffK

[anca.taciuc]

Hi mancubus,

It was number of columns.

Thank you very much,

I am in the learning process on VBA, this being my second project.

[mancubus]

you are welcome. so it worked for you?

i missed declaration of i and j



Sub vbax_54454_Random_Cells_Values()

Dim SimNum As Long, LastCol As Long, i As Long, j As Long

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Input").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Input"

SimNum = InputBox("Please enter the number of simulations")

With Worksheets("Intervals")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 1 To SimNum
For j = 1 To LastCol
RandRowNo = WorksheetFunction.RandBetween(2, .Cells(.Rows.Count, j).End(xlUp).Row)
Worksheets("Input").Cells(i, j).Value = .Cells(RandRowNo, j).Value
Next j
Next i
End With

End Sub

[anca.taciuc]

Yes it worked. :)

Thank you again





you are welcome. so it worked for you?

i missed declaration of i and j



Sub vbax_54454_Random_Cells_Values()

Dim SimNum As Long, LastCol As Long, i As Long, j As Long

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Input").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Input"

SimNum = InputBox("Please enter the number of simulations")

With Worksheets("Intervals")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 1 To SimNum
For j = 1 To LastCol
RandRowNo = WorksheetFunction.RandBetween(2, .Cells(.Rows.Count, j).End(xlUp).Row)
Worksheets("Input").Cells(i, j).Value = .Cells(RandRowNo, j).Value
Next j
Next i
End With

End Sub

[mancubus]

welcome again.

pls mark the thread as solved from thread tool (top right corner of first message) for future references...

[snb]

Another approach:


Sub M_snb()
sn = Sheets("intervals").Cells(1).CurrentRegion

For jj = 1 To UBound(sn, 2)
sn(1, jj) = Application.Count(Application.Index(sn, [row(1:5)], jj))
Next

ReDim sp(Val(InputBox("number of iterations")) - 1, 3)
For j = 0 To UBound(sp)
For jj = 0 To UBound(sp, 2)
sp(j, jj) = sn(2 + Int(Rnd * sn(1, jj + 1)), jj + 1)
Next
Next

If [not(isref(Input!A1))] Then Sheets.Add.Name = "input"
Sheets("input").Cells(1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub