VBA to look and replace text in same row, different column

[xChelly]

I'm relatively new to VBA and I'm trying to write a code that will look at the receiving person column for Peter and will replace the Target Person and Target Fruit columns to match what's in the Sending Person and Sending Fruit.

I used this thread here to figure out the code, but that thread is looking values for different sheets. All of my data is in the same sheet, same rows, but different columns.


Raw Data:

Sending Person	Sending Fruit	Receiving Person	Receiving Fruit	Target Person	Target Fruit
George	Strawberries	Peter	Jackfruit	Amy	Blackberries
Kelsey	Blueberries	Peter	Jackfruit	Keith	Grapes

<tbody>
</tbody>

Should Be:

Sending Person	Sending Fruit	Receiving Person	Receiving Fruit	Target Person	Target Fruit
George	Strawberries	Peter	Jackfruit	George	Strawberries
Kelsey	Blueberries	Peter	Jackfruit	Kelsey	Blueberries

<tbody>
</tbody>

Any help would be appreciated.

 

[PCL]

In others words you want to replace last 2 columns by the 2 first one ??

 

[PCL]

Try
Adjust "WkWd" to what you need

Sub Treat()
Const WkWd As String = "Peter"
Dim LR   As Integer, I  As Integer
    LR = Cells(Rows.Count, 1).End(3).Row
    For I = 2 To LR
        If (Cells(I, "C") = WkWd) Then
            Cells(I, "E") = Cells(I, "A")
            Cells(I, "F") = Cells(I, "B")
        End If
    Next
    MsgBox ("Job Done")
End Sub

 

[xChelly]

In others words you want to replace last 2 columns by the 2 first one ??

I tried to simplify the table, but it's actually something like this.

ID	Purpose	Sending Fname	Sending Lname	Sending Fruit	Receiving Fname	ReceivingLName	Receiving Fruit	Target Person	Target Fruit
PDN	INTC	George	Smith	Berries	Peter	X	Jackfruit	Keith	Grapes
SFO	INTC	Kelsey	Da	Apples	Daisy	XX	Grapes	Michael	Berries
SEA	INTC	Amy	B	Bananas	Peter	X	Jackfruit	Kanna	Dragonfruit

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

But yeah that's essentially what I want to do with extra tables/data inbetween the other columns.