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

xChelly

New Member
Joined
Apr 10, 2018
Messages
2
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 PersonSending FruitReceiving PersonReceiving FruitTarget PersonTarget Fruit
GeorgeStrawberriesPeterJackfruitAmyBlackberries
KelseyBlueberriesPeterJackfruitKeithGrapes

<tbody>
</tbody>

Should Be:
Sending PersonSending FruitReceiving PersonReceiving FruitTarget PersonTarget Fruit
GeorgeStrawberriesPeterJackfruitGeorgeStrawberries
KelseyBlueberriesPeterJackfruitKelseyBlueberries

<tbody>
</tbody>


Any help would be appreciated.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
In others words you want to replace last 2 columns by the 2 first one ??
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Try
Adjust "WkWd" to what you need

Code:
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

New Member
Joined
Apr 10, 2018
Messages
2
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.

IDPurposeSending FnameSending LnameSending FruitReceiving FnameReceivingLNameReceiving FruitTarget PersonTarget Fruit
PDNINTCGeorgeSmithBerriesPeterXJackfruitKeithGrapes
SFOINTCKelseyDaApplesDaisyXXGrapesMichaelBerries
SEAINTCAmyBBananasPeterXJackfruitKannaDragonfruit

<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.
 

Forum statistics

Threads
1,181,774
Messages
5,931,963
Members
436,811
Latest member
OscarBoots_23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top