Nested For Each Loops

[tyhand]

Hi all!

I have two ranges. Range 1 and Range 2.
Range 2 is within Range 1.

I want to iterate through the ranges to
check for duplicates. For example:

Range 1 equals
1, 2, 3, 2, 5

Range 2 (within Range 1) equals
2, 3, 2, 5

I'm using a nested For Each Loop to
check the ranges -->

For each x in Range 1
For each y in Range 2
If x.Text = y.Text Then
do something
Else
do something else
End if
Next y
Next x

I want to compare each element (x) in Range
1 to each element in range 2 but I want
to make count downwards.

For example, on the first loop x = 1 and y = 2.
On the second loop x = 1, but I want y to start
at 3 this time. On the third loop x still equals
1, but I want y to start at 2 this time etc.

How can I do this? Thanks in advance. Peace!

 

[bryanbayfield]

for each c in Range1
    if intersect(c, Range2) is nothing then
            ' Cell in range1 not in range2, add appropriate code for this event here
            else:
                  'Cell is in range1 and range2, add app. code
            end if
next

No need for nested loops

 

[bryanbayfield]

ooops! wotta mistaka to maka! I'm very embarrassed!

You wanted to check duplicate values, not whether cells shared the same range.

Your code should do what you want, but the following would be more efficient:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim range1 As Variant, range2 As Variant
range1 = Range("a1", Range("a8")).Value
range2 = Range("a6", Range("a8")).Value
For i = 1 To UBound(range1)
    For j = 1 To UBound(range2)
        If range1(i, 1) = range2(j, 1) Then
            MsgBox "Match! Range 1 cell = " & i & ", Range 2 cell = " & j
            Else: MsgBox "No Match"
            End If
        Next j
    Next i
End Sub[code]

Just replace the message boxes with what you want.

Apologies again - mentally, I've already gone home for the weekend.

One way you could make it go faster would be to create another range, eg one_minus_two, check whether any value in that is also in range2. This makes more sense because once you know the cells that are in range2 are duplicates because range2 is contained within range1.

Time for a strong coffee ...

Bryan.

 

[segmentationfault]

Yeah, I think there is a better way to do it than that polynomial algorithm. It will make smoke come out of your computer if you use a large enough input range. No really, I'm not lying.

It seems that if Range2 is a subset of Range1, every element of Range2 will be a duplicate of something in Range1 by definition. It would be more efficient to iterate through Range2 and compare those values to the cells of Range1 that do not intersect Range2. When you're done with Range2, you know that the total number of duplicates is Range2 + the duplicates you found.

But maybe I'm reading too much into it.

 

[tyhand]

Hey,

Worry not, you don't lose 'cool points' for making mistakes.
Actually, it's me who's embarrassed.
I should've mentioned that I'm using Word, >not< Excel.
Shishkabob! Sorry 'bout that.

bryanbayfield, you're code actually looks like one I've written B/4 for Excel, but w/o the ubound. It took me all
of 30 days to create it. But, you know what they say...
great minds think alike!

Segmentationfault mentioned that I should iterate through
range2. I actually thought of that but I think that programatically it'll do the same thing as iterating through range1.

The code is suppossed to make any dups turn to the color red and any non-dups to blue. However, after the second iteration any reds get changed to blue again. Which is why
I'm trying to make the iterations on range2 count down.