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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sending Person[/TD]
[TD]Sending Fruit[/TD]
[TD]Receiving Person[/TD]
[TD]Receiving Fruit[/TD]
[TD]Target Person[/TD]
[TD]Target Fruit[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Strawberries[/TD]
[TD]Peter[/TD]
[TD]Jackfruit[/TD]
[TD]Amy[/TD]
[TD]Blackberries[/TD]
[/TR]
[TR]
[TD]Kelsey[/TD]
[TD]Blueberries[/TD]
[TD]Peter[/TD]
[TD]Jackfruit[/TD]
[TD]Keith[/TD]
[TD]Grapes[/TD]
[/TR]
</tbody>[/TABLE]

Should Be:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sending Person[/TD]
[TD]Sending Fruit[/TD]
[TD]Receiving Person[/TD]
[TD]Receiving Fruit[/TD]
[TD]Target Person[/TD]
[TD]Target Fruit[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Strawberries[/TD]
[TD]Peter[/TD]
[TD]Jackfruit[/TD]
[TD]George[/TD]
[TD]Strawberries[/TD]
[/TR]
[TR]
[TD]Kelsey[/TD]
[TD]Blueberries[/TD]
[TD]Peter[/TD]
[TD]Jackfruit[/TD]
[TD]Kelsey[/TD]
[TD]Blueberries[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 951"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Purpose[/TD]
[TD]Sending Fname[/TD]
[TD]Sending Lname[/TD]
[TD]Sending Fruit[/TD]
[TD]Receiving Fname[/TD]
[TD]ReceivingLName[/TD]
[TD]Receiving Fruit[/TD]
[TD]Target Person[/TD]
[TD]Target Fruit[/TD]
[/TR]
[TR]
[TD]PDN[/TD]
[TD]INTC[/TD]
[TD]George[/TD]
[TD]Smith[/TD]
[TD]Berries[/TD]
[TD]Peter[/TD]
[TD]X[/TD]
[TD]Jackfruit[/TD]
[TD]Keith[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]SFO[/TD]
[TD]INTC[/TD]
[TD]Kelsey[/TD]
[TD]Da[/TD]
[TD]Apples[/TD]
[TD]Daisy[/TD]
[TD]XX[/TD]
[TD]Grapes[/TD]
[TD]Michael[/TD]
[TD]Berries[/TD]
[/TR]
[TR]
[TD]SEA[/TD]
[TD]INTC[/TD]
[TD]Amy[/TD]
[TD]B[/TD]
[TD]Bananas[/TD]
[TD]Peter[/TD]
[TD]X[/TD]
[TD]Jackfruit[/TD]
[TD]Kanna[/TD]
[TD]Dragonfruit[/TD]
[/TR]
</tbody>[/TABLE]

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