0

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Adrian Muntean
  • 3
  • 1
  • `Range("item" & i)` should work – Tim Williams Dec 13 '22 at 18:06
  • A general rule of thumb, you don't need/want to use Select, especially not if you're going to loop through your items (unless maybe it's for learning purposes and you want to follow the code more?) https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Notus_Panda Dec 13 '22 at 18:11
  • Range("item" & i) gives out the error " The item with the specified name was not found, when i=1, but it's able to find it if I directly look for "item1". Select here is use for learning purposes, I'll jump to adding other actions as soon as I'm able to select it – Adrian Muntean Dec 13 '22 at 18:18
  • my mistake, Range("item"&i) works but I need to select the referecend cell in that named range, so the code is Range([indirect("item" & i)]). So basically indirect function fails if it's not purely string written directly in vba and instead a concatenation. – Adrian Muntean Dec 13 '22 at 19:08

1 Answers1

0
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
JosephC
  • 917
  • 4
  • 12