Solved: Find and Replace marco p Revisited

[maxflia10]

I have this code which does a find and replace based on a criteria, all works fine except the last part. Here's the code,

[vba]

Sub Vacation_Trade_Xshift()
Dim x As Long, i As String
With Range("B6:H119")
For x = 122 To 139
If Range("C" & x) = 8 Then
.Replace What:=Range("B" & x), Replacement:=Range("J" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
If UCase(Range("I" & x)) = "TRADE" Then
.Replace What:=Range("E" & x), Replacement:=Range("H" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
If UCase(Range("I" & x)) = "XSHIFT" Then
i = Range("H" & x).Value
.Replace What:=Range("E" & x), Replacement:=Range("H" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("E" & x).Value = i
End If
Next x
End With
Range("E122").Select
End Sub

[/vba]

If I122 = XSHIFT, Find E122 and H122 in the range B6:H119 and replace E122 with H122 and H122 with E122, Do until row 139 .

[Bob Phillips]

I just ran a simple test and it worked as I expected.

In what way does it not work, what happens?

.

[maxflia10]

It doesn't flip flop (switch names). Say name A is in E122 and Name B in H122 with Xshift in I122, with Xshift being the criteria to switch names in E122 and H122 in the range from B6:H119. If name A resides in B10 and name B in C10, the end result would be Name A switched to C10 and Name B to B10.

[Bob Phillips]

It doesn't flip flop (switch names). Say name A is in E122 and Name B in H122 with Xshift in I122, with Xshift being the criteria to switch names in E122 and H122 in the range from B6:H119. If name A resides in B10 and name B in C10, the end result would be Name A switched to C10 and Name B to B10.

Aah, I see. You are replacing the other value in row 122, not row 10.

The difficulty is that Replace is a batch operation and you have no idea which cells were replaced, so you need a different approach. But ... you say B10/C10 should switch. If it is fouind iin C10, should C10/D10 switch? And if so, what happens when it is found in H10?

.

[maxflia10]

The names to switch can be found anywhere in B6:H119. The names (criteria) to be switched are in range E122:I139, always in the same row. As an example for the criteria,

E122 Name A
H122 Name B
I122 Xshift

Name A and Name B resides anywhere in B6:H119. But for example's sake, Name A in B43, Name B H100. After running the macro, the end result would be...

Name A in H100 and Name B in B43.

Thanks for taking the time!

[Bob Phillips]

The names to switch can be found anywhere in B6:H119. The names (criteria) to be switched are in range E122:I139, always in the same row. As an example for the criteria,

E122 Name A
H122 Name B
I122 Xshift

Name A and Name B resides anywhere in B6:H119. But for example's sake, Name A in B43, Name B H100. After running the macro, the end result would be...

Name A in H100 and Name B in B43.

Thanks for taking the time!

Okay, this is really kludgy but I cannot think of a better way

[VBA]
Sub Vacation_Trade_Xshift()
Dim x As Long, i As String
With Range("B6:H119")
For x = 122 To 139
If Range("C" & x) = 8 Then
.Replace What:=Range("B" & x), Replacement:=Range("J" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
If UCase(Range("I" & x)) = "TRADE" Then
.Replace What:=Range("E" & x), Replacement:=Range("H" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
If UCase(Range("I" & x)) = "XSHIFT" Then
i = Range("E" & x).Value
.Replace What:=Range("E" & x), Replacement:="__temp__", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:=Range("H" & x), Replacement:=Range("E" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="__temp__", Replacement:=Range("H" & x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next x
End With
Range("E122").Select
End Sub
[/VBA]

[maxflia10]

xld,

Thank-you very much! It works a treat!

I appreciate your help!