JasonAizkalns
JasonAizkalns

Reputation: 20463

Looping through multiple named ranges and going to similar named range VBA

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:

Upvotes: 1

Views: 1709

Answers (3)

Slai
Slai

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

user3598756
user3598756

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

John Coleman
John Coleman

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

Related Questions