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.

Improve this question

1 Answer 1

Reset to default
8

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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)
Side question: Is there a way to determine which item in the list the cell is currently at (1st, 2nd, 3rd, etc)?
In Excel 365, I get the Run-time error '424: Object Required on the Set inputrange ... line. Any idea why?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.