All possible outcomes from drop-down lists

[BillTSC]

Hi All,

First time posting, been a long-time reader.

I have a question that I'm sure would have been looked in to before but cant seem to get a fix via solver or any of my own VBA work.

I have a model with 96 different possible outcomes dependent on the variables in 4 different drop down menus (2 have 4 options, 1 has 3 and 1 has 2). Depending on the selection in each drop down I arrive at a slightly different value outcome from the model.

I was hoping to create a macro that would take all the possible outcomes and copy them to a table effectively with 96 rows and 2 columns. (Iteration number, just 1-96, and outcome value).

Any thoughts on how best to accomplish this or does anyone have some similar code that could be edited to work?

Really appreciate it.

Bill

 

[DanteAmor]

Maybe something like this:

Sub All_Possible_Outcomes()
    'All possible outcomes from drop-down lists
    Dim h As Worksheet
    Dim n As Integer, i As Integer, j As Integer, k As Integer, m As Integer
    Set h = Sheets("Table")
    h.Rows("2:" & Rows.Count).ClearContents
    n = 2
    y = 1
    '
    For i = 1 To 4
        For j = 1 To 4
            For k = 1 To 3
                For m = 1 To 2
                    h.Cells(n, "A").Value = y
                    h.Cells(n, "B").Value = "Menu 1 op " & i & ", Menu 2 op " & j & _
                                            " ,Menu 3 op " & k & " ,Menu 2 op " & m
                    y = y + 1
                    n = n + 1
                Next
            Next
        Next
    Next
    MsgBox "End"
End Sub

 

[BillTSC]

Hi Dante,

Thanks so much for this. Will have a look and a play around with it and let you know how it goes.

Thanks again
Bill

 

[BillTSC]

Hi Dante,

This is a great base to start from thank you. The code you provided will copy over the outcomes of the possible dropdown options themselves (with a bit of tweaking obviously). The dropdown menu's on sheet 1, when changed, will change the value of cell K7. Depending on how I arrange those dropdowns, that number in k7 will vary. I was hoping to create something that would run all drop down menu's and copy the different outcomes of k7 to another sheet in that table form.

I will use your code as a base and see what tweaks I can make. Any other thoughts would be appreciated.

Thanks so much,

Bill

 

[DanteAmor]

You can also use an ActiveX combobox, every time you change the value in the combo1 you can load the combo2.