VBA Loop through named ranges - using Indirect on variants-CodePudding

I'm trying to loop through the multiple named ranges, every range is just one cell, on a column, so it starts with item1, item2...item100.Each of these ranges has a cell reference in it (like "AM100"). I want to be able to set up a for/while loop that just selects(and does something) each of the referenced cells in those named ranges. I'm stuck at VBA not considering "item"&"1" the same with "item"&"i" when i =1 and from what I deduct this is purely a data type issue. It's able to refer to a pure string range but when it's variant/string type (which any concatenation of string or converted string variable results in).

item1 range has in it $AM$10 reference. That cell, has a value in it. Eventually I want to change the values in multiple similar cells by referring to the name ranges that hold their reference.

Without the "for" loop, I've tested the following:

Sub test()
Dim i as integer

i=1
'These don't work
Range([indirect("item"&CSTR(i))]).Select
Range([indirect("item"&i)]).Select

'What works is, but this is not useful since I want to loop through i:
Range([indirect("item" & "1")]).Select
Range([indirect("item1")]).Select

CodePudding user response：

Sub Test()
    Dim oDefName As Name
    For Each oDefName In ThisWorkbook.Names
        If Left(UCase(oDefName.Name), 4) = "ITEM" Then
            Range(oDefName.RefersToRange.Value).Select
        End If
    Next
End Sub

Note: There is no error checking to ensure that the value within the named range is actually a cell reference.

Edit- Above is how I would solve the problem. Indirect is an in cell function and not usable directly in vba. Below is how you would get the indirect functionality in VBA with a counter.

Range(Range("Item" & oCounter).Value).Select