Loop values from data validation list vba

[white_flag]

Hello,
I have a validation data in "C3" with the following structure:

 =INDEX(INDIRECT(B3);;1)

indirect() function will take the name of table (created as an range) and from that, table column 1 will become an drop down data validation list.
But now, I like to go from those values one by one (loop).

How can I do this?
Thanks in advance!

[mana]

Sub test()
    Dim v

    v = Evaluate(Range("C3").Validation.Formula1)
    MsgBox Join(WorksheetFunction.Transpose(v), vbLf)

End Sub

[Kenneth Hobs]

If you attach a file, I could help probably.

[white_flag]

Dim rng As Range, cell As Range


Set rng = Evaluate(ActiveSheet.Range("C3").Validation.Formula1)

error 1004

[white_flag]

see attachment!

[mana]

Sub test2()
    Dim v
     
    v = Evaluate(Range("C3").Validation.Formula1)
    With Worksheets.Add
        .Cells(1).Resize(UBound(v)).Value = v
    End With
     
End Sub


Sub test3()
    Dim v
    Dim i As Long
     
    v = Evaluate(Range("C3").Validation.Formula1)
    For i = LBound(v, 1) To UBound(v, 1)
        MsgBox v(i, 1)
    Next
     
End Sub

[white_flag]

on

Evaluate(Range("C3").Validation.Formula1)

I have error 2015

[Kenneth Hobs]

Sub Main()  
  Dim c As Range
  For Each c In Range(Range("B3").Value2)
     'Debug.Print c.Address, c.Value
     Debug.Print c.Address(External:=True), c.Value
  Next c
End Sub

[white_flag]

@Kenneth
I have an Error 1004


I see that vba canot evaluate:

Application.Evaluate("=INDEX(INDIRECT(" & ActiveSheet.Range("C3") & " );;1)")

[white_flag]

@Kenneth


I do not have an error (my bad) ... But the loop is going to all table. It is possible to go just on the first column?

[white_flag]

I putted like this. (it is working)... What do you think?

Sub Main()
    Dim c As Range
    For Each c In Range(Range("B3").Value2)
        If c.Column = 1 Then Debug.Print c.Value
    Next c
End Sub

[Kenneth Hobs]

Sure. e.g.

Sub Main()    
  Dim c As Range
    For Each c In WorksheetFunction.Index(Range(Range("B3").Value2), 0, 1)
        Debug.Print c.Address(External:=True), c.Value
    Next c
End Sub

Yes, you can do that but adds an extra step and thus time. For say < 100,000 rows, it probably won't make much difference in time.

Of course there are other ways if all you wanted were the values. I would use array methods. Howsoever, 2 solutions should suffice. There are most always many ways to do these things. Some are a bit faster but the time spent to make a faster routine may or may not pay off.

[white_flag]

You are correct!

In the end I putted like this:

Sub Main()
    Dim c As Range
    For Each c In Range(Range("B3").Value).Columns(1).Cells
        Debug.Print c.Address(External:=True), c.Value
    Next c
End Sub

Thanks for your help and time! Have a nice day