LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 36
Default Opposite of Intersect function - an example

No question here, just a procedure for the archive.
Search criteria: reverse intersect mutually exclusive ranges
ranges not shared range not shared interesect opposite opposite of
intersect
intersect reverse mutually exclusive range
evalute intersect select opposite intersects
indentify intersect opposite

a modification of some code that Jim Rech, Excel MVP posted on August
25, 2004:

Sub IntersectOppositeExample()

'Example of how to select the mutually exclusive
'portion(s) of two ranges that intersect in Excel
Dim Rng1, Rng2, AllRange, ExcludeRange As Range
Dim NewRg, CurrCell, AntiRange As Range

On Error Resume Next
Set Rng1 = Application.InputBox( _
prompt:="Select 1st Range of Cells
to Evaluate", Type:=8)
Set Rng2 = Application.InputBox( _
prompt:="Select 2nd Range of Cells
to Evaluate", Type:=8)
Set AllRange = Union(Rng1, Rng2)
Set ExcludeRange = Intersect(Rng1, Rng2)


For Each CurrCell In AllRange.Cells
If Intersect(CurrCell, ExcludeRange) Is Nothing Then
If NewRg Is Nothing Then
Set NewRg = CurrCell
Else
Set NewRg = Union(NewRg, CurrCell)
End If
End If
Next
Set AntiRange = NewRg
AntiRange.Select
End Sub
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What function is the opposite of SUM? rsilver Excel Discussion (Misc queries) 4 November 19th 05 06:56 PM
Opposite of SUM function is ?? amkazen Excel Discussion (Misc queries) 1 April 18th 05 07:13 PM
The opposite of IF function gives #VALUE for value = 0 gizmo Excel Worksheet Functions 2 December 30th 04 01:36 PM
how to get the intersect cell data out using vba or function mango Excel Worksheet Functions 1 December 30th 04 09:46 AM
opposite of intersect David C. Excel Programming 2 August 25th 04 05:45 PM


All times are GMT +1. The time now is 02:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"