Loop through range objects based on name

danp2

New Member
Joined
Dec 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Set Rng_search = Range(box(x) & i)

I have tried the following but get runtime error '424' object required:
VBA Code:
Set Rng_search = box(x) & i

The following works but then I cannot loop through:
VBA Code:
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.

VBA Code:
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
You cannot loop through variables like that, try it like
VBA Code:
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.
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,339
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
You cannot loop through variables like that, try it like
VBA Code:
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

New Member
Joined
Dec 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,804
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Dec 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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.
 

Forum statistics

Threads
1,176,089
Messages
5,901,345
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top