Loop through range objects based on name

[danp2]

Hi,

I am sure that this is a simple answer but after a lot of googling and reading through threads I still cannot get it to work. I am trying to search through a number of ranges in a loop and populate an multi-array if the range includes the value I am looking for. The next part all works ok but stuck with the first part of the loop to pickup the ranges.

I have tried different ways of doing it but I seem to be getting a run-time '1004' error on picking up the search range
The issue is:

Set Rng_search = Range(box(x) & i)

I have tried the following but get runtime error '424' object required:

Set Rng_search = box(x) & i

The following works but then I cannot loop through:

Set Rng_search = Mon_r1

I am guessing that it is something to do with the mix of text and numbers but I cannot get it working with converting.

As I am new to vba so I am very prone to simple errors, therefore before I start changing it (populate multi-dimension array rather than multiple ranges) I was hoping that there is a simple answer!

I have put a simplified version below.

Dim i As Long, x As Long, y As Long
Dim day_select As Variant
Dim Rng_search As Range

day_select = Array("Mon_r", "Tues_r", "Weds_r", "Thurs_r", "Fri_r", "Sat_r", "Sun_r")

'included for testing - each range is set as 'public' in another module and populated in from the activesheet before calling the next module.  Also includes Wed_, Thurs_ etc.
Invoice_Num = 1
Set Mon_r1 = ActiveSheet.Range("B9:B14"): Set Mon_r2 = ActiveSheet.Range("C9:C14"): Set Mon_r3 = ActiveSheet.Range("D9:D14"): Set Mon_r4 = ActiveSheet.Range("E9:E14")
Set Mon_r5 = ActiveSheet.Range("F9:F14"): Set Mon_r6 = ActiveSheet.Range("G9:G14"): Set Mon_r7 = ActiveSheet.Range("H9:H14")

Set Tues_r1 = ActiveSheet.Range("B18:B23"): Set Tues_r2 = ActiveSheet.Range("C18:C23"): Set Tues_r3 = ActiveSheet.Range("D18:D23"): Set Tues_r4 = ActiveSheet.Range("E18:E23")
Set Tues_r5 = ActiveSheet.Range("F18:F23"): Set Tues_r6 = ActiveSheet.Range("G18:G23"): Set Tues_r7 = ActiveSheet.Range("H18:H23")

Invoice_Num = 1 'again this is set elsewhere from a [collection].count and can go up to 100.

For x = 0 To 6                                                                              'picks up the text from the day_select array
        For i = 1 To 7                                                                       'for periods in the day
            Set Rng_search = Range(day_select(x) & i)
                  y = 0                                                                          ' just for testing
                  For Each cell In Rng_search
                        If cell.Value = List(0, Invoice_Num) Then            'looks up value in an pre-populated array
                        y = 1                                                                    'just for testing
                        End If
                   Next cell
                  msgbox y                                                                   'just for testing
       Next i                                                                                    'move to next period, day etc.
Next x                                                                                          'move to next period, day etc.

 

[Fluff]

You cannot loop through variables like that, try it like

Dim i As Long, x As Long, y As Long
Dim day_select As Variant
Dim Rng_search As Range

day_select = Array("B9:B14", "B18:B23")

'included for testing - each range is set as 'public' in another module and populated in from the activesheet before calling the next module.  Also includes Wed_, Thurs_ etc.
Invoice_Num = 1
'Set Mon_r1 = ActiveSheet.Range("B9:B14"): Set Mon_r2 = ActiveSheet.Range("C9:C14"): Set Mon_r3 = ActiveSheet.Range("D9:D14"): Set Mon_r4 = ActiveSheet.Range("E9:E14")
'Set Mon_r5 = ActiveSheet.Range("F9:F14"): Set Mon_r6 = ActiveSheet.Range("G9:G14"): Set Mon_r7 = ActiveSheet.Range("H9:H14")
'
'Set Tues_r1 = ActiveSheet.Range("B18:B23"): Set Tues_r2 = ActiveSheet.Range("C18:C23"): Set Tues_r3 = ActiveSheet.Range("D18:D23"): Set Tues_r4 = ActiveSheet.Range("E18:E23")
'Set Tues_r5 = ActiveSheet.Range("F18:F23"): Set Tues_r6 = ActiveSheet.Range("G18:G23"): Set Tues_r7 = ActiveSheet.Range("H18:H23")

Invoice_Num = 1 'again this is set elsewhere from a [collection].count and can go up to 100.

For x = 0 To UBound(day_select)                                                                              'picks up the text from the day_select array
        For i = 0 To 6                                                                       'for periods in the day
            Set Rng_search = Range(day_select(x)).Offset(, i)
                  y = 0                                                                          ' just for testing
                  For Each cell In Rng_search
                        If cell.Value = List(0, Invoice_Num) Then            'looks up value in an pre-populated array
                        y = 1                                                                    'just for testing
                        End If
                   Next cell
                  MsgBox y                                                                   'just for testing
       Next i                                                                                    'move to next period, day etc.
Next x                                                                                          'move to next period, day etc.

 

[RoryA]

You can't build up variable names as strings like that. It looks like you could just use a 2D array of ranges to me.

 

[danp2]

You cannot loop through variables like that, try it like

Dim i As Long, x As Long, y As Long
Dim day_select As Variant
Dim Rng_search As Range

day_select = Array("B9:B14", "B18:B23")

'included for testing - each range is set as 'public' in another module and populated in from the activesheet before calling the next module.  Also includes Wed_, Thurs_ etc.
Invoice_Num = 1
'Set Mon_r1 = ActiveSheet.Range("B9:B14"): Set Mon_r2 = ActiveSheet.Range("C9:C14"): Set Mon_r3 = ActiveSheet.Range("D9:D14"): Set Mon_r4 = ActiveSheet.Range("E9:E14")
'Set Mon_r5 = ActiveSheet.Range("F9:F14"): Set Mon_r6 = ActiveSheet.Range("G9:G14"): Set Mon_r7 = ActiveSheet.Range("H9:H14")
'
'Set Tues_r1 = ActiveSheet.Range("B18:B23"): Set Tues_r2 = ActiveSheet.Range("C18:C23"): Set Tues_r3 = ActiveSheet.Range("D18:D23"): Set Tues_r4 = ActiveSheet.Range("E18:E23")
'Set Tues_r5 = ActiveSheet.Range("F18:F23"): Set Tues_r6 = ActiveSheet.Range("G18:G23"): Set Tues_r7 = ActiveSheet.Range("H18:H23")

Invoice_Num = 1 'again this is set elsewhere from a [collection].count and can go up to 100.

For x = 0 To UBound(day_select)                                                                              'picks up the text from the day_select array
        For i = 0 To 6                                                                       'for periods in the day
            Set Rng_search = Range(day_select(x)).Offset(, i)
                  y = 0                                                                          ' just for testing
                  For Each cell In Rng_search
                        If cell.Value = List(0, Invoice_Num) Then            'looks up value in an pre-populated array
                        y = 1                                                                    'just for testing
                        End If
                   Next cell
                  MsgBox y                                                                   'just for testing
       Next i                                                                                    'move to next period, day etc.
Next x                                                                                          'move to next period, day etc.

Thank you very much for the fast response. Yes this was my next option - I use the ranges elsewhere so I didn't want to change unless I had to.

 

[danp2]

You can't build up variable names as strings like that. It looks like you could just use a 2D array of ranges to me.

Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[rlv01]

Late to the party here, but if you are trying to build larger range from a collection of already-existing named ranges, another possibility is to leverage the Application.Union statement

Sub SimpleExample()
    Dim Rng_search As Range, RCell As Range
    Dim N As Name
  
    Set Rng_search = Nothing
    For Each N In ThisWorkbook.Names 'Workbook level names
    'For Each N In ActiveSheet.Names  'Worksheet level names
        Select Case Left(N.Name, Len(N.Name) - 1)
            Case "Mon_r", "Tues_r", "Weds_r", "Thurs_r", "Fri_r", "Sat_r", "Sun_r"
                If Rng_search Is Nothing Then
                    Set Rng_search = N.RefersToRange
                Else
                    Set Rng_search = Union(Rng_search, N.RefersToRange)
                End If
        End Select
    Next N
  
    If Not Rng_search Is Nothing Then
        MsgBox "Search Range is: " & Rng_search.Address(, , , True)
        For Each RCell In Rng_search
            '
            ' do whatever you want here
            '
        Next RCell
    End If
End Sub

 

[danp2]

Late to the party here, but if you are trying to build larger range from a collection of already-existing named ranges, another possibility is to leverage the Application.Union statement

Sub SimpleExample()
    Dim Rng_search As Range, RCell As Range
    Dim N As Name
 
    Set Rng_search = Nothing
    For Each N In ThisWorkbook.Names 'Workbook level names
    'For Each N In ActiveSheet.Names  'Worksheet level names
        Select Case Left(N.Name, Len(N.Name) - 1)
            Case "Mon_r", "Tues_r", "Weds_r", "Thurs_r", "Fri_r", "Sat_r", "Sun_r"
                If Rng_search Is Nothing Then
                    Set Rng_search = N.RefersToRange
                Else
                    Set Rng_search = Union(Rng_search, N.RefersToRange)
                End If
        End Select
    Next N
 
    If Not Rng_search Is Nothing Then
        MsgBox "Search Range is: " & Rng_search.Address(, , , True)
        For Each RCell In Rng_search
            '
            ' do whatever you want here
            '
        Next RCell
    End If
End Sub

Thank you for the response and the option. I am going to have a play tomorrow and see which solution works best for me.