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!
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!
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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.