ExcelBanter - Loops

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Loops (https://www.excelbanter.com/excel-programming/429972-loops.html)

Can I loop soubroutine, if not, how to I make the loop work. I want to run
the three subroutines m1 , m2 and m3 when the subroutine 'Go' is selected. I
tried to right the loop inside the routine itself and didnt know how to do
this.

I also want to run loops such that at a time two values of a combobox change
when everything else is constant.

Any help would be appreciated, thx.

Sub m1()
If Sheet1.ComboBox1 = Sheet1.Range("B152") And _
Sheet1.ComboBox2 = Sheet1.Range("C151") And _
Sheet1.ComboBox3 = Sheet1.Range("D151") And _
Sheet1.ComboBox4 = Sheet1.Range("E151") And _
Sheet1.ComboBox5 = Sheet1.Range("F151") Then

Sheet31.Range("D19") = Sheet3.Range("G16")
Sheet31.Range("E19") = Sheet13.Range("G16")
Sheet31.Range("F19") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
End If
End Sub

Sub m2()
If Sheet1.ComboBox1 = Sheet1.Range("B151") And _
Sheet1.ComboBox2 = Sheet1.Range("C152") And _
Sheet1.ComboBox3 = Sheet1.Range("D151") And _
Sheet1.ComboBox4 = Sheet1.Range("E151") And _
Sheet1.ComboBox5 = Sheet1.Range("F151") Then

Sheet31.Range("D20") = Sheet3.Range("G16")
Sheet31.Range("E20") = Sheet13.Range("G16")
Sheet31.Range("F20") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
End If
End Sub

Sub m3()
If Sheet1.ComboBox1 = Sheet1.Range("B151") And _
Sheet1.ComboBox2 = Sheet1.Range("C153") And _
Sheet1.ComboBox3 = Sheet1.Range("D151") And _
Sheet1.ComboBox4 = Sheet1.Range("E151") And _
Sheet1.ComboBox5 = Sheet1.Range("F151") Then

Sheet31.Range("D21") = Sheet3.Range("G16")
Sheet31.Range("E21") = Sheet13.Range("G16")
Sheet31.Range("F21") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
End If
End Sub

Private Sub go_Click()
'Low Risk
If Sheet1.Range("B169") = Sheet1.Range("B171") Then
Sheet1.ComboBox1 = Sheet1.Range("B151")
Sheet1.ComboBox2 = Sheet1.Range("C151")
Sheet1.ComboBox3 = Sheet1.Range("D151")
Sheet1.ComboBox4 = Sheet1.Range("E151")
Sheet1.ComboBox5 = Sheet1.Range("F151")
Sheet1.ComboBox6 = Sheet1.Range("G152")
Sheet1.ComboBox7 = Sheet1.Range("H153")
Sheet1.ComboBox8 = Sheet1.Range("I151")
Sheet1.ComboBox9 = Sheet1.Range("J154")
Sheet1.ComboBox10 = Sheet1.Range("K152")

Sheet31.Range("D6") = Sheet3.Range("G16")
Sheet31.Range("E6") = Sheet13.Range("G16")
Sheet31.Range("F6") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
'Intermediate - 1 Risk
ElseIf Sheet1.Range("B169") = Sheet1.Range("B172") Then
Sheet1.ComboBox6 = Sheet1.Range("G152")
Sheet1.ComboBox7 = Sheet1.Range("H153")
Sheet1.ComboBox8 = Sheet1.Range("I151")
Sheet1.ComboBox9 = Sheet1.Range("J154")
Sheet1.ComboBox10 = Sheet1.Range("K152")

Dim i As String
m1
m2
m3
For i = m1 To m3
Next i

End If
End Sub

Never mind, I figured it out, I made a few logic errors

"aqualibra" wrote:

Can I loop soubroutine, if not, how to I make the loop work. I want to run
the three subroutines m1 , m2 and m3 when the subroutine 'Go' is selected. I
tried to right the loop inside the routine itself and didnt know how to do
this.

I also want to run loops such that at a time two values of a combobox change
when everything else is constant.

Any help would be appreciated, thx.

Sub m1()
If Sheet1.ComboBox1 = Sheet1.Range("B152") And _
Sheet1.ComboBox2 = Sheet1.Range("C151") And _
Sheet1.ComboBox3 = Sheet1.Range("D151") And _
Sheet1.ComboBox4 = Sheet1.Range("E151") And _
Sheet1.ComboBox5 = Sheet1.Range("F151") Then

Sheet31.Range("D19") = Sheet3.Range("G16")
Sheet31.Range("E19") = Sheet13.Range("G16")
Sheet31.Range("F19") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
End If
End Sub

Sub m2()
If Sheet1.ComboBox1 = Sheet1.Range("B151") And _
Sheet1.ComboBox2 = Sheet1.Range("C152") And _
Sheet1.ComboBox3 = Sheet1.Range("D151") And _
Sheet1.ComboBox4 = Sheet1.Range("E151") And _
Sheet1.ComboBox5 = Sheet1.Range("F151") Then

Sheet31.Range("D20") = Sheet3.Range("G16")
Sheet31.Range("E20") = Sheet13.Range("G16")
Sheet31.Range("F20") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
End If
End Sub

Sub m3()
If Sheet1.ComboBox1 = Sheet1.Range("B151") And _
Sheet1.ComboBox2 = Sheet1.Range("C153") And _
Sheet1.ComboBox3 = Sheet1.Range("D151") And _
Sheet1.ComboBox4 = Sheet1.Range("E151") And _
Sheet1.ComboBox5 = Sheet1.Range("F151") Then

Sheet31.Range("D21") = Sheet3.Range("G16")
Sheet31.Range("E21") = Sheet13.Range("G16")
Sheet31.Range("F21") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
End If
End Sub

Private Sub go_Click()
'Low Risk
If Sheet1.Range("B169") = Sheet1.Range("B171") Then
Sheet1.ComboBox1 = Sheet1.Range("B151")
Sheet1.ComboBox2 = Sheet1.Range("C151")
Sheet1.ComboBox3 = Sheet1.Range("D151")
Sheet1.ComboBox4 = Sheet1.Range("E151")
Sheet1.ComboBox5 = Sheet1.Range("F151")
Sheet1.ComboBox6 = Sheet1.Range("G152")
Sheet1.ComboBox7 = Sheet1.Range("H153")
Sheet1.ComboBox8 = Sheet1.Range("I151")
Sheet1.ComboBox9 = Sheet1.Range("J154")
Sheet1.ComboBox10 = Sheet1.Range("K152")

Sheet31.Range("D6") = Sheet3.Range("G16")
Sheet31.Range("E6") = Sheet13.Range("G16")
Sheet31.Range("F6") = Sheet16.Range("G16")

Sheets("Risk Factor").Select
Worksheets("Risk Factor").Range("A1").Select
'Intermediate - 1 Risk
ElseIf Sheet1.Range("B169") = Sheet1.Range("B172") Then
Sheet1.ComboBox6 = Sheet1.Range("G152")
Sheet1.ComboBox7 = Sheet1.Range("H153")
Sheet1.ComboBox8 = Sheet1.Range("I151")
Sheet1.ComboBox9 = Sheet1.Range("J154")
Sheet1.ComboBox10 = Sheet1.Range("K152")

Dim i As String
m1
m2
m3
For i = m1 To m3
Next i

End If
End Sub