anca.taciuc
12-04-2015, 05:14 AM
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
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