Nested looping through data validation cells

Question

I am working with 2 cells that are both using data validation lists (cell B3 and B4 in the below). The list item chosen in each cell is used in a formula that outputs 2 results (one in C21 and the other in C22). What I am trying to do is present all iterations and their result in C21 and C22. How I have approached this is by looping through all data validation items in B4 and for each item in B4, loop through all options in B3.

There are 8 different options in B3 and 4 different options in B4. Using Offset, I am presenting the outputs of this in a separate tab called "Sensitivity Output" which I would expect to be a table 8 columns wide and 16 rows deep.

In addition to this, I am getting an error of "Object required" on the line "Set divRange = Evaluate(divCell.Validation.Formula1)"

Code below as presented which partially works but is not presenting the desired result at the moment.

I am new to VBA but as far as I understand this it should be working?

Thanks in advance for any help!

Sub Sensitivty()
'
' Sensitivty Macro
'
    Dim ebitCell As Range
    Dim ebitRange As Range
    Dim c As Range
    
    Set ebitCell = Sheets("Assumptions Input").Range("B3")
    Set ebitRange = Evaluate(ebitCell.Validation.Formula1)
    
    Dim divCell As Range
    Dim divRange As Range
    Dim d As Range
    
    Set divCell = Sheets("Assumptions Input").Range("B4")
    Set divRange = Evaluate(divCell.Validation.Formula1)
    
    'Looping through items in B4
     For Each d In divRange
      divCell.Value = d.Value
     
     'For each item in B4, loop through each of the items in B3
            For Each c In ebitRange
                   
                    ebitCell.Value = c.Value
                    Worksheets("Assumptions Input").Activate
                    Range("C21:C22").Select
                    Selection.Copy
                    Worksheets("Sensitivity Output").Activate
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                     :=False, Transpose:=False
                    ActiveCell.Offset(0, 1).Select
            Next c
          
        ActiveCell.Offset(2, -8).Select
            
        Next d
    
    Set ebitCell = Nothing
    Set ebitRange = Nothing
    Set divCell = Nothing
    Set divRange = Nothing

End Sub

Don't use activate or select: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Jun 08 '22 at 14:13
@SJR it should give the full list of items included in the data validation in the cell specified — NickVert, Jun 08 '22 at 14:25
It returns the follow: "60% Margin,33% Margin,25.79% Margin,Breakeven". Not sure how to check that on the actual code, but that is from the data validation rule on B4 — NickVert, Jun 08 '22 at 15:09
There isn't a validation formula? Sorry, this is where I am new and thought that was just part of syntax — NickVert, Jun 08 '22 at 15:43
Ok so how are you populating the DV list? Perhaps you can post an [mcve]. — SJR, Jun 08 '22 at 15:45
`Dim arr; arr = Split(divCell.Validation.Formula1, ",")` then loop over the array `arr`. Your posted code is designed for a validation list which uses a linked range, not one where you've manually entered the items as a list directly in the DV dialog. — Tim Williams, Jun 08 '22 at 15:50
@TimWilliams I changed this to a linked range (with 1 small correction to the code posted) and works perfectly. Answered the question too as it was obvious this is the only issue. Thanks for the pointer and damn good spot! — NickVert, Jun 08 '22 at 16:26

score 0 · Answer 1 · answered Jun 08 '22 at 16:25

As pointed out in the comments, this is actually a working piece of code (slight adjustment to original to correct a small error made). The issue I faced was that the code was built to work off data validation used a linked range rather than a list (which I had in place at D4). This works as expected when using a linked range for both B3 and B4 in the example given

Nested looping through data validation cells

1 Answers1