Find and replace again...!!!!

[reena]

I got this macro in my previous post.

Sub Replace()
Cells.Replace What:="DataA", Replacement:="DataB",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End Sub

This will find the DataA value in excel sheet and will replace it by
DataB value.

Now I want to modify it. This should find the combination of values and
replace it. I mean macro should find that if the value of Column C is
DataA1 and value of Column D is DataA2 (same row), then these values
should get replaced by DataB1 and DataB2 respectively.

Reena

 

[Dave Peterson]

One way is to just look for the match in column C and then check column D. This
routine uses Replace (not edit|replace). And that was added in xl2k. So it
won't work in xl97.

You could use application.substitute() instead of Replace, but that's case
sensitive--so you'll have to match strings exactly.


Option Explicit
Sub Replace2()

Dim WhatToFind1 As String
Dim ReplaceWith1 As String
Dim WhatToFind2 As String
Dim ReplaceWith2 As String

Dim FoundCell As Range
Dim FirstAddress As String

Dim wks As Worksheet

Set wks = Worksheets("sheet1")

WhatToFind1 = "dataa1"
ReplaceWith1 = "DataB1"

WhatToFind2 = "dataa2"
ReplaceWith2 = "DataB2"

With wks
With .Range("c:c")
Set FoundCell = .Cells.Find(what:=WhatToFind1, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlNext, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'nothing to do
Else
FirstAddress = FoundCell.Address
Do
If InStr(1, FoundCell.Offset(0, 1).Value, _
WhatToFind2, vbTextCompare) > 0 Then
FoundCell.Value _
= Replace(expression:=FoundCell.Value, _
Find:=WhatToFind1, _
Replace:=ReplaceWith1, _
Start:=1, _
Count:=-1, _
compare:=vbTextCompare)
FoundCell.Offset(0, 1).Value _
= Replace(expression:=FoundCell.Offset(0, 1).Value, _
Find:=WhatToFind2, _
Replace:=ReplaceWith2, _
Start:=1, _
Count:=-1, _
compare:=vbTextCompare)
End If
Set FoundCell = .FindNext(FoundCell)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If

End With
End With

End Sub

========
Another way would be to apply data|filter|autofilter to those two columns.

Then use a custom filter on column C to show the rows that contain DataA1. Then
use a custom filter in column D to show the rows that contain DataA2.

Now select the visible cells in Column C and do your edit|Replace.

Then select the visible cells in column D and do edit|replace one more time.

Then remove the data|filter|autofilter.

(But I didn't think of this until I was done!)

 

[reena]

Hi Dave,

Thank you very much for your efforts. But this is not working. And I am
not able to understand wts the problme

 

[Dave Peterson]

"It isn't working" isn't enough to help me help you, either.

What did you try? Did you step through the code?