Find and Replace problem

Hi,
all

I have a data like this.
ColA ColB ColC ColD
Red HG VG WH
Yellow T G Y
they Red G Y
Yellow DG MG GH


I want to create a macro which finds Red in colA and ColB. If it finds in
colB then next two cell should be replaced by null(Blank) value means empty.
One cell before that in col A should also be replaced by null value. Ifred
is in ColA then next two cell should have null(blank) value. Here ia what I
want my sheet to look like.

ColA ColB ColC ColD
Red
Yellow T G Y
Red
Yellow DG MG GH
Any help on this would be highly appreciated.

Thanks in advance
--
Kittie

Some clarification needed. Are we only looking for "Red" or do you want to
compare every value in column A for a match in column B and then clear the
cells you said? If there is no match in column B then leave the adjacent
cells to column A as is?

Mike F
"Lolly" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> all
>
> I have a data like this.
> ColA ColB ColC ColD
> Red HG VG WH
> Yellow T G Y
> they Red G Y
> Yellow DG MG GH
>
>
> I want to create a macro which finds Red in colA and ColB. If it finds in
> colB then next two cell should be replaced by null(Blank) value means
> empty.
> One cell before that in col A should also be replaced by null value.
> Ifred
> is in ColA then next two cell should have null(blank) value. Here ia what
> I
> want my sheet to look like.
>
> ColA ColB ColC ColD
> Red
> Yellow T G Y
> Red
> Yellow DG MG GH
> Any help on this would be highly appreciated.
>
> Thanks in advance
> --
> Kittie

This code worked in my test as you described. It will test each item in
column A for a match in column B and do what you want.

Sub ClearMatches()
Dim c As Range, c2 As Range
Dim Rng As Range, Rng2 As Range
Dim Lrow As Long, Lrow2 As Long

Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Lrow2 = Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range("A1:A" & Lrow)
Set Rng2 = Range("B1:B" & Lrow2)

For Each c In Rng
If c.Value = "" Then GoTo 1
For Each c2 In Rng2
If c.Value = c2.Value Then
Range("B" & c.Row & ":D" & c.Row).ClearContents
Range("A" & c2.Row & ",C" & c2.Row & ":D" &
c2.Row).ClearContents
End If
Next c2
1:
Next c
End Sub

Mike F
"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> Some clarification needed. Are we only looking for "Red" or do you want to
> compare every value in column A for a match in column B and then clear the
> cells you said? If there is no match in column B then leave the adjacent
> cells to column A as is?
>
> Mike F
> "Lolly" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> all
>>
>> I have a data like this.
>> ColA ColB ColC ColD
>> Red HG VG WH
>> Yellow T G Y
>> they Red G Y
>> Yellow DG MG GH
>>
>>
>> I want to create a macro which finds Red in colA and ColB. If it finds in
>> colB then next two cell should be replaced by null(Blank) value means
>> empty.
>> One cell before that in col A should also be replaced by null value.
>> Ifred
>> is in ColA then next two cell should have null(blank) value. Here ia what
>> I
>> want my sheet to look like.
>>
>> ColA ColB ColC ColD
>> Red
>> Yellow T G Y
>> Red
>> Yellow DG MG GH
>> Any help on this would be highly appreciated.
>>
>> Thanks in advance
>> --
>> Kittie
>
>

Test this on a copy of your data:

Sub subFindRed()
Dim sAddr As String
Dim rng As Range, rng1 As Range
Set rng = Cells(Rows.Count, 1)
Set rng1 = Range("A:B").Find(What:="Red", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
If rng1.Column = 1 Then
rng1.Offset(0, 1).Resize(, 3).ClearContents
ElseIf rng1 = 2 Then
rng1.Offset(0, -1).ClearContents
rng1.Offset(0, 1).Resize(, 2).ClearContents
End If
Set rng1 = Range("A:B").FindNext(rng1)
Loop While rng1.Address <> sAddr
End If
End Sub


--
Regards,
Tom Ogilvy

"Lolly" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> all
>
> I have a data like this.
> ColA ColB ColC ColD
> Red HG VG WH
> Yellow T G Y
> they Red G Y
> Yellow DG MG GH
>
>
> I want to create a macro which finds Red in colA and ColB. If it finds in
> colB then next two cell should be replaced by null(Blank) value means
empty.
> One cell before that in col A should also be replaced by null value.
Ifred
> is in ColA then next two cell should have null(blank) value. Here ia what
I
> want my sheet to look like.
>
> ColA ColB ColC ColD
> Red
> Yellow T G Y
> Red
> Yellow DG MG GH
> Any help on this would be highly appreciated.
>
> Thanks in advance
> --
> Kittie

Hi,
Mike

I am not only looking for Red. I am looking for some other words also. It 's
not necessary to match with colB or ColA

I will give u e.g
ColA ColB ColC ColD

Hred Mead GHM MHG GH
HG JH MEAD HJ LK
GH NG MJ KJ
GH MJ NG MJ


Ideally macro should search for mead which is a part of text in the cell and
then clear contents next to it and before that. Here is what it should look
like after macro is run


ColA ColB ColC ColD

Hred Mead
JH MEAD
GH NG MJ KJ
GH MJ NG MJ


I hope this clarifies your question. Thanks a lot mike.
This I need to do for thousands of rows.





--
Kittie


"Mike Fogleman" wrote:

> Some clarification needed. Are we only looking for "Red" or do you want to
> compare every value in column A for a match in column B and then clear the
> cells you said? If there is no match in column B then leave the adjacent
> cells to column A as is?
>
> Mike F
> "Lolly" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > all
> >
> > I have a data like this.
> > ColA ColB ColC ColD
> > Red HG VG WH
> > Yellow T G Y
> > they Red G Y
> > Yellow DG MG GH
> >
> >
> > I want to create a macro which finds Red in colA and ColB. If it finds in
> > colB then next two cell should be replaced by null(Blank) value means
> > empty.
> > One cell before that in col A should also be replaced by null value.
> > Ifred
> > is in ColA then next two cell should have null(blank) value. Here ia what
> > I
> > want my sheet to look like.
> >
> > ColA ColB ColC ColD
> > Red
> > Yellow T G Y
> > Red
> > Yellow DG MG GH
> > Any help on this would be highly appreciated.
> >
> > Thanks in advance
> > --
> > Kittie
>
>
>

I gave you a solution that will do this. You just need to change

Set rng1 = Range("A:B").Find(What:="Red", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


to

Set rng1 = Range("A:B").Find(What:="MEAD", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)



--
Regards,
Tom Ogilvy


"Lolly" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> Mike
>
> I am not only looking for Red. I am looking for some other words also. It
's
> not necessary to match with colB or ColA
>
> I will give u e.g
> ColA ColB ColC ColD
>
> Hred Mead GHM MHG GH
> HG JH MEAD HJ LK
> GH NG MJ KJ
> GH MJ NG MJ
>
>
> Ideally macro should search for mead which is a part of text in the cell
and
> then clear contents next to it and before that. Here is what it should
look
> like after macro is run
>
>
> ColA ColB ColC ColD
>
> Hred Mead
> JH MEAD
> GH NG MJ KJ
> GH MJ NG MJ
>
>
> I hope this clarifies your question. Thanks a lot mike.
> This I need to do for thousands of rows.
>
>
>
>
>
> --
> Kittie
>
>
> "Mike Fogleman" wrote:
>
> > Some clarification needed. Are we only looking for "Red" or do you want
to
> > compare every value in column A for a match in column B and then clear
the
> > cells you said? If there is no match in column B then leave the adjacent
> > cells to column A as is?
> >
> > Mike F
> > "Lolly" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > > all
> > >
> > > I have a data like this.
> > > ColA ColB ColC ColD
> > > Red HG VG WH
> > > Yellow T G Y
> > > they Red G Y
> > > Yellow DG MG GH
> > >
> > >
> > > I want to create a macro which finds Red in colA and ColB. If it finds
in
> > > colB then next two cell should be replaced by null(Blank) value means
> > > empty.
> > > One cell before that in col A should also be replaced by null value.
> > > Ifred
> > > is in ColA then next two cell should have null(blank) value. Here ia
what
> > > I
> > > want my sheet to look like.
> > >
> > > ColA ColB ColC ColD
> > > Red
> > > Yellow T G Y
> > > Red
> > > Yellow DG MG GH
> > > Any help on this would be highly appreciated.
> > >
> > > Thanks in advance
> > > --
> > > Kittie
> >
> >
> >