Excel disjunction

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!!

Answer this question

Excel disjunction

WXS123
This could be one way:

The Find method of the Range object searches for data and returns a Range object that represents the first cell where the data is found.

If you store the Row properties of the Ranges returned when "IMM" and "FUTURE" are found, you can then compare these values against the Row property when "DATE" is found and if they match ignore the row.

The VBA help on the Find method has an example showing use of the Address property but it can be easily amended to use the Row property. It should give you some ideas.

Another way would be using the Intersect method, first the Range where "IMM" is found with the Range where "DATE" is found. If the ranges intersect ignore the range in the search, then do the same with "FUTURE" and "DATE".
Excel disjunction

Answer this question