PDA

View Full Version : [SOLVED] Loop values from data validation list vba



white_flag
09-20-2016, 05:42 AM
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
09-20-2016, 06:24 AM
Sub test()
Dim v

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

End Sub

Kenneth Hobs
09-20-2016, 06:36 AM
If you attach a file, I could help probably.

white_flag
09-20-2016, 06:36 AM
Dim rng As Range, cell As Range


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



error 1004

white_flag
09-20-2016, 06:53 AM
see attachment!

mana
09-20-2016, 06:57 AM
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
09-20-2016, 07:02 AM
on



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


I have error 2015

Kenneth Hobs
09-20-2016, 07:36 AM
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
09-20-2016, 07:42 AM
@Kenneth
I have an Error 1004


I see that vba canot evaluate:


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

white_flag
09-20-2016, 07:46 AM
@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
09-20-2016, 07:58 AM
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
09-20-2016, 08:03 AM
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
09-20-2016, 08:16 AM
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 :)