2

I have a cell with a validation list that changes depending on other settings. Is it possible to iterate through that cell in code? Is it also possible to set that cell to the nth item in the list?

Example: enter image description here The drop down for E2 depends on what was selected for D2. I would like to be able to iterate through whatever list is in E2 without having to check D2.

Thank you.

KingKong
  • 411
  • 3
  • 10
  • 26

1 Answers1

7

This will work for you

Sub loopthroughvalidationlist()
     Dim inputRange As Range
     Dim c As Range
     Set inputRange = Evaluate(Range("D2").Validation.Formula1)
     For Each c In inputRange
        '... do something with c.Value
    Next c
End Sub
Jeanno
  • 2,769
  • 4
  • 23
  • 31
  • This works, thanks! And to answer the 2nd part of my own question, you can set it to one of the drop down items by doing: Range("D2") = inputRange(x) – KingKong Jun 03 '15 at 00:26
  • Side question: Is there a way to determine which item in the list the cell is currently at (1st, 2nd, 3rd, etc)? – KingKong Jun 03 '15 at 00:30
  • In Excel 365, I get the `Run-time error '424: Object Required` on the `Set inputrange ...` line. Any idea why? – BruceWayne Dec 10 '18 at 19:48