Excel VBA: Dynamic Data Validation Input Message with Index Match

Question

With Data Validation Input Message, for each cell in a named range ("MyRange"), I'd like to match against Table1[Column1] and index in the value from Table1[Column2].

I need this to loop, since as it doesn't, the active cell remains constant, and the entire range has the same Input Message. I'm not committed to the formula INDEX MATCH, it just needs to perform that function.

Sub Tester()
    Dim mycell As String
    mycell = ActiveCell
        With Range("MyRange").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, _
        Operator:=xlBetween, Formula1:="=INDIRECT(Test1&Test2)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = Application.WorksheetFunction.Index(Range("Table1[Column2]"), _
        Application.WorksheetFunction.Match(mycell, Range("Table1[Column1]"), 0))
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

Thank you for any help!

There's no loop? You just have with the Range Validation...Where do you expect it to loop? At the *very least* you'll want to change the activecell after your first go around with, say, `Cells(activecell.row+1,ActiveCell.Column)`. ...but also don't do that, as it's [best to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BruceWayne, Aug 14 '17 at 22:31
I don't expect it to. That's the issue I am trying to resolve. :) — AlivePresumably, Aug 14 '17 at 22:33
you can do a web search about looping in a range .... http://www.excel-easy.com/vba/examples/loop-through-defined-range.html — jsotola, Aug 14 '17 at 23:33

score 0 · Answer 1 · answered Aug 16 '17 at 03:20

Wrap this around the code you have now. Basically, you want to paste your code into the line that reads Debug.Print . . . with some very minor modifications.

Sub LoopRange()

    Dim rCell As Range
    Dim rRng As Range

    Set rRng = Sheet1.Range("A1:A6")

    For Each rCell In rRng.Cells
        Debug.Print rCell.Address, rCell.Value
    Next rCell

End Sub

Loop through each cell in a range of cells when given a Range object

Excel VBA: Dynamic Data Validation Input Message with Index Match

1 Answers1