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