Find and Replace problem

[Guest]

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

 

[Mike Fogleman]

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

 

[Mike Fogleman]

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 & "" & c.Row).ClearContents
Range("A" & c2.Row & ",C" & c2.Row & "" &
c2.Row).ClearContents
End If
Next c2
1:
Next c
End Sub

Mike F

 

[Tom Ogilvy]

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

 

[Guest]

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.

 

[Tom Ogilvy]

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)