Search cells C and D for partial match in each row.

[nvdp2002]

Hi All,

Firstly thank you to all of you who have been helping each other. The posts here have gotten me through tones of work.

I need a little help with a search and copy macro. Im usually ok with this kind of thing but the problem is i cant find a way to search 2 adjacent cells for a partial match.
I can use the Instr function on a single cell down through all the rows but i cant get it to check both cells. In my case its C and D.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]BB[/TD]
[TD]CB[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]BA[/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]CB[/TD]
[TD]AA[/TD]
[/TR]
</tbody>[/TABLE]

In the second and third column i need the adjacent cells to be checked for "C" for example.
After C is found i will copy the row to another sheet in the same workbook.

Thank you very much for your help.

Nasser

 

[AlphaFrog]

Try something like this...

    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Range("C2", Range("C" & Rows.cont).End(xlUp))
        [COLOR=darkblue]If[/COLOR] InStr(1, cell.Value, "C", 1) And InStr(1, cell.Offset(, 1).Value, "C", 1) [COLOR=darkblue]Then[/COLOR]
           cell.EntireRow.Copy
           [COLOR=green]'Paste it somewhere[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR]

Or use the Autofilter method.

    [COLOR=darkblue]With[/COLOR] Range("A1", Range("D" & Rows.cont).End(xlUp))
        .AutoFilter Field:=3, Criteria1:="*C*"  [COLOR=green]'filter column C[/COLOR]
        .AutoFilter Field:=4, Criteria1:="*C*"  [COLOR=green]'filter column D[/COLOR]
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy
        [COLOR=green]'Paste it somewhere[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

 

[nvdp2002]

Wow..... I should have been able to work that out. Thanks allot for the help. The offset option is exactly what i needed. You rock !