I have a macro that searches a spreadsheet for certain cells that contains
certain values. In some cases I need to specify that I olny want to search
parts of the spreadsheet. For example I first search for the word "IMM" and
then I search for the word "FUTURE". I then want to search to rest of the
spreadsheet for the word "DATE" i.e. search all rows except for the rows that
conatins IMM and FUTURE. I know that there is a union thing in Excel but is
there a thing such as disunion? My code looks like:

Dim searchRange As Range
Set searchRange = Range(Cells(secID.Row, 1), Cells(secID.Row, 100))
Set secID = Worksheets("Sheet").Range(Cells(segment.Offset(i, j).Row,
1), Cells(3000, 100)).Find("Date", LookIn:=xlValues)

I need to handle this varibly because it changes all the time. Please help
me out if you can! Thank you very much!!

Arne,

You would need to make up a range that includes all the IMM and FUTURE result - using Union, let's
call the range rInF- and then when your find DATE, you would need to make sure that

If Intersect(rInF.EntireRow,secID.EntireRow) Is Nothing Then
'They don't share a row
Else
'They do share a row
End If

HTH,
Bernie
MS Excel MVP


"Arne Hegefors" wrote in message
...
I have a macro that searches a spreadsheet for certain cells that contains
certain values. In some cases I need to specify that I olny want to search
parts of the spreadsheet. For example I first search for the word "IMM" and
then I search for the word "FUTURE". I then want to search to rest of the
spreadsheet for the word "DATE" i.e. search all rows except for the rows that
conatins IMM and FUTURE. I know that there is a union thing in Excel but is
there a thing such as disunion? My code looks like:

Dim searchRange As Range
Set searchRange = Range(Cells(secID.Row, 1), Cells(secID.Row, 100))
Set secID = Worksheets("Sheet").Range(Cells(segment.Offset(i, j).Row,
1), Cells(3000, 100)).Find("Date", LookIn:=xlValues)

I need to handle this varibly because it changes all the time. Please help
me out if you can! Thank you very much!!