Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace problem
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace problem
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" wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace problem
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace problem
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace problem
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find/Replace Problem | Excel Discussion (Misc queries) | |||
Find and Replace problem | Excel Discussion (Misc queries) | |||
Find-Replace problem | Excel Discussion (Misc queries) | |||
Find replace problem | Excel Discussion (Misc queries) | |||
Problem with Find and Replace | Excel Worksheet Functions |