How to refer to values from a dynamic named range in VBA

[mintz]

I have this table where where each column is defined in the Name Manager

How do I pull a value (e.g. Region) into a variable, using the names I defined?

I'd like to loop through the Regions column, this is what I'm trying:

For i = i to lrow

   If Region = "Quebec" Then

   do something here ...

   End If

Next

This gives me a type mismatch error

 

[dreid1011]

Check some of the code posted here near the bottom of the thread:

https://stackoverflow.com/questions/13039437/loop-through-cells-in-named-range

 

[tonyyy]

mintz,

You might consider the following...

Sub LoopThroughNamedRange()
Dim lrow As Long, i As Long
lrow = Range("Region").Rows.Count
For i = 1 To lrow
    If Cells(i, Range("Region").Column).Value = "Quebec" Then
        Cells(i, Range("Region").Column).Select
        MsgBox Cells(i, Range("Region").Column).Address
    End If
Next i
End Sub

Cheers,

tonyyy

 

[GWteB]

How do I pull a value (e.g. Region) into a variable, using the names I defined?

Hopefully this is of some help ...

[COLOR=darkblue]Sub[/COLOR] Test()
    [COLOR=darkblue]Call[/COLOR] [COLOR=#ff0000]SearchWithinRange[/COLOR](argRangeName:="[U]region[/U]", argValue:="[U]quebec[/U]")
[COLOR=darkblue]End[/COLOR] Sub
[COLOR=darkblue]



Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=#ff0000]SearchWithinRange[/COLOR](argRangeName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], argValue [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR])

    [COLOR=green]'~~> search in a given range for a specific string[/COLOR]
    
    [COLOR=green]' ( example:  argRange = "Region",  argValue = "Quebec" )[/COLOR]


    [COLOR=darkblue]Dim[/COLOR] rTemp       [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell       [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] lIndex      [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]


    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]                    [COLOR=green]' ignore runtime errors[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rTemp = Range(argRangeName)         [COLOR=green]' make proper object reference to desired range[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0                         [COLOR=green]' enable runtime error tracking[/COLOR]
    
    [COLOR=green]' check if range exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] rTemp [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'~~> at this point there is nothing to do[/COLOR]
        MsgBox "The name " & Chr(34) & argRangeName & Chr(34) & " is not present in the list of named ranges"
    
    [COLOR=darkblue]Else[/COLOR]
            
        [COLOR=green]'~~> at this point the name of the range is recognized[/COLOR]
        [COLOR=green]' an index might be conveniënt[/COLOR]
        lIndex = 0
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rTemp
        
            [COLOR=green]' keep track of rCell's index within rTemp[/COLOR]
            lIndex = lIndex + 1
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]StrComp[/COLOR](rCell.Formula, argValue, vbTextCompare) = 0 [COLOR=darkblue]Then[/COLOR]
                
                [COLOR=green]' do something here  ...[/COLOR]
                
                [COLOR=green]' example 1[/COLOR]
                MsgBox Chr(34) & argValue & Chr(34) & " found at " & [B]rCell.Address[/B] & ", " & vbCrLf & _
                                                       "at index " & [B]lIndex[/B] & _
                                               " of named range [" & [B]rTemp.Name.NameLocal[/B] & "]" & _
                                                    " refers to [" & [B]rTemp.Name[/B] & "]," & vbCrLf & _
                                                   "at rownumber " & [B]rCell.Row[/B] & " on this worksheet."

            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] rCell
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=green]'clean up[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rTemp = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rCell = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Kind regards