I would like to use the index number in a For loop in a range name. Please see below. I am receiving an "Object required" error. Thank you for your help.
Sincerely,
Dante
Dim i As Integer
For i = 2 To 25
If Not Intersect(range_1, range_ & i) Is Nothing Then
MsgBox ("Error! range_1 and range_" & i & " overlap!")
End If
Next i
You cannot append numbers to the variable names. The Intersect function takes ranges objects, so you might want to write:
Dim i As Integer
For i = 2 To 25
If Not Intersect(Range("range_1"), Range("range_" & i)) Is Nothing Then
MsgBox ("Error! range_1 and range_" & i & " overlap!")
End If
Next i
Of course, I'm assuming that you have properly named your ranges with "range_1", "range_2" etc.
EDIT (by Doug): Here's a version that ensures you are checking in the correct sheet. In this case it's set to ActiveSheet, but you can adjust that. Note that this checks for named ranges actually defined in the worksheet, not ranges set as variables in your code:
Sub CheckIntersect()
Dim ws As Worksheet
Dim i As Long
Set ws = ActiveSheet 'adjust if necessary
For i = 2 To 25
If Not Intersect(ws.Range("range_1"), ws.Range("range_" & i)) Is Nothing Then
MsgBox ("Error! range_1 and range_" & i & " overlap!")
End If
Next i
End Sub
Intersect and so I noticed that you did too above. It won't compile until it's added.
range_1 and range_2 exist? Did you name a range in your spreadsheet with this name?I leave the previous answer since, in case of named ranges, it's correct and has also been improved by someone else, so users that look for problems with named ranges might find it useful.
On the other hand, what you probably meant is that you assigned the range to a variable, such as (of course this is just an example, i cannot know how you made your assignment)
Set range_1 = Range("A1")
Set range_2 = Range("A2")
'...
Set range_25 = Range("A25)
Unfortunately you cannot loop like that through variable names. But what you can do is creating a collection of ranges, so that you will be able to access them lately by using an index to iterate through them:
Dim ranges As Collection: Set ranges = New Collection
ranges.Add range_1
ranges.Add range_2
'...
ranges.Add range_25
More in general, add the range to the collection every time you define it. Please note that even if this range will change at a later stage, the range inside your list will change as well because the Add method creates a shallow copy of the object (in other words, it is still pointing to the original object).
Now, the collection of ranges is storing your range objects and you will be able to access them by index. For example, in your specific case:
For j = 2 To 25
If Not Intersect(ranges(1), ranges(j)) Is Nothing Then
As a good practice, remember that every time you will want to iterate through a "humanly indexable" series of objects/variables as in your case, you will often might want to cast them into a collection because the name of the variables cannot be create/changed from the code itself.
range_1orrange_i? Named range? You probably know thatrangeis an object. so without defining, you are trying to useintersect, so error comes.