Reputation: 20463
I would like to check multiple named ranges for a boolean True/False
value. I need to check the 1-cell validation ranges (in order) and if the result is True
, I need to .Select
the corresponding named range (i.e., the named range without the corresponding "validation_"
prefix and exit the subroutine. The following code works, but it's not DRY.
Here's a snippet to get the gist of the question, but this If-ElseIf
continues for many other named ranges:
If Range("validation_name") = True Then
Range("name").Select
Exit Sub
ElseIf Range("validation_category") = True Then
Range("category").Select
Exit Sub
ElseIf Range("validation_subcategory") = True Then
Range("subcategory").Select
Exit Sub
' ... and many more...
Possibilities/Questions:
collection
instead? for
loop or a while
loop would be better?Upvotes: 1
Views: 1709
Reputation: 22876
You can loop through all of the named ranges with something like:
Dim xlName As Name
For each xlName In ActiveWorkbook.Names
If xlName.Name Like "validation_*" And Range(xlName.Name) = True Then
Application.Goto Replace(xlName.Name, "validation_", ""), True
Exit Sub
End If
Next
or specify them like this
For each strName In Split("name category subcategory")
If Range("validation_" & strName) = True Then
Application.Goto strName, True
Exit Sub
End If
Next
Update
Sorry, I did not read the whole question. Seems like you can use some kind of key - value Collection
pairs = Array( Array( "validation_name", "name" ), _
Array( "validation_category", "category" ), _
Array( "validation_subcategory", "subcategory" ) )
For each pair In pairs
If Range( pair(0) ) = True Then
Application.Goto pair(1), True
Exit Sub
End If
Next
Upvotes: 2
Reputation: 29421
some "extra dry" codes
Sub main()
Dim a As Variant
For Each a In Array("name", "category", "subcategory")
If Range("valid_" & a).Value = True Then
Range(a).Select
Exit Sub
End If
Next a
End Sub
Sub main2()
Dim r As Range, f As Range
Set r = Union(Range("validation_date"), Range("validation_name"), Range("validation_subcategory"), Range("validation_category"))
Set f = r.Find(what:="true", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not f Is Nothing Then Range(Replace(f.name.name, "validation_", "")).Select
End Sub
Upvotes: 3
Reputation: 51998
I assume that these are 1-cell ranges. If so, the following should work:
Sub SelectRange()
Dim i As long, A As Variant
A = Array("validation_name", "validation_category", "validation_subcategory")
For i = 0 To UBound(A)
If Range(A(i)).Value = True Then
Range(Split(A(i),"_")(1)).Select
Exit Sub
End If
Next i
End Sub
Upvotes: 3