Aaaaargh - Please help me with Intersect..!!

[DanUK]

Hi guys,

I really hope someone out there in VBA land is able to offer me some assistance with this...even if that is to say that I can't do what I want to !!

I'm writing a some VBA code that tests to see if two non contiguous ranges overlap. I'm using the Intersect method to test for this like so:

If Not Intersect(usedRange, wordRng) Is Nothing Then

Once I've established that I do indeed have over lapping ranges (and after a further test to ensure that the overlap range is no bigger than 1 cell...), I then want to test if the values of the two overlapping ranges are the same. I tried the following code:

strAdd = Intersect(usedRange, wordRng).Address
Debug.Print usedRange.Range(strAdd).Value, wordRng.Rang(strAdd).Value

Now the above code doesn't work. It is syntactically correct and the two ranges (usedRange & wordRng) can be referenced in this way BUT not using the return value of the Intersect method as this is outside of the two ranges!! Does this make sense???

Does anyone know how I can obtain the values of the overalap of the intersecting ranges??

Thanks in advance.

Dan

 

[Von Pookie]

If the ranges are overlapping and the overlap is limited to just one cell, you only need that one cell--not that same cell for both ranges as they would be one and the same.

strAdd = Intersect(usedRange, wordRng).Address
Debug.Print Range(strAdd).Value

 

[xenou]

Hi, maybe you are overthinking this one. Does this work:

Dim r As Range
Dim myVal as Variant
Set r = Intersect(UsedRange, wordRng)
If Not r Is Nothing Then     [COLOR="SeaGreen"]'//proceed if there is an overlap[/COLOR]
    If r.Count = 1 Then      [COLOR="seagreen"]'//proceed if overlap is one cell[/COLOR]
        myVal = r.Value
    End If
End If

 

[DanUK]

Thanks guys!

It was late last night when i was doing this and clearly i was suffering from brain rot!! Reading your replies made it obvious to me where i was going wrong. Only one real value here and not two...

Thanks again

Dan