Find A Word in A Column and Replace it With Text in other Columns Using VBA

lookman

New Member
Joined
Oct 19, 2019
Messages
3
NameAddressDescription
John2nd Street, NYA1A1 is 35 year old, A1A1 is NewYork ....
Kevin4th street, FL
A1A1 is 35 year old, A1A1 is Florida ....

<tbody>
</tbody>

Manly5Th street, NY
A1A1 is 35 year old, A1A1 is NewYork ....

<tbody>
</tbody>
A1A1 is 35 year old, A1A1 is NewYork ....

<tbody>
</tbody>

Drake77th Street, NY
A1A1 is 35 year old, A1A1 is NewYork ....

<tbody>
</tbody>

Bale
A1A1 is 35 year old, A1A1 is NewYork ....

<tbody>
</tbody>


<tbody>
</tbody>


I have a CSV Table with Rows and Columns like the Above and i want a VBA Code that would find A1A1 in the Description Column and Replace with a name corresponding to respective.

NameAddressDescription
John2nd Street, NYJohn is 35 Years old, John is NewYork
Kent4th Street, FLKent is 35 Years old, Kent is Florida

<tbody>
</tbody>

I have over 30,000 record which i need to find and replace in a stop time, so can anyone help me out please.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi & welcome to MrExcel.
How about
Code:
Sub lookman()
    With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(@="""","""",substitute(@,""A1A1""," & .Offset(, -2).Address & "))", "@", .Address))
    End With
End Sub
 
Upvote 0
Hi & welcome to MrExcel.
How about
Code:
Sub lookman()
    With Range("C1", Range("C" & Rows.Count).End(xlUp))
        .Value = Evaluate(Replace("if(@="""","""",substitute(@,""A1A1""," & .Offset(, -2).Address & "))", "@", .Address))
    End With
End Sub

Please can you clarify me on this code, because when i used it, Cleared all text in the description Rows throwing back an error like #VALUE !
 
Upvote 0
Not near as short a code as Fluff's but you could try it anyway.
Code assumes that the name is in Column A, Address in Column B and Description in Column C.
If Column D is free to use as a help Column, this would work.
Code:
Sub Maybe_A()
Application.ScreenUpdating = False
    With Range(Cells(2, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))
        .Offset(, 1).Formula = "=SUBSTITUTE(RC[-1],""A1A1"",RC[-3])"
        .Value = .Offset(, 1).Value
    End With
Columns(4).ClearContents
Application.ScreenUpdating = True
End Sub
If there are unknown amount of Columns to the right of Column C used, this would work.
Code:
Sub Maybe_B()
Dim nc As Long
nc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column + 1
Application.ScreenUpdating = False
    With Range(Cells(2, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))
        .Offset(, nc - 3).Formula = "=SUBSTITUTE(RC[-" & nc - 3 & "],""A1A1"",RC[-" & nc - 1 & "])"
        .Value = .Offset(, nc - 3).Value
    End With
Columns(nc).ClearContents
Application.ScreenUpdating = True
End Sub
BTW, you don't have to quote whole posts. Just a bunch of extra clutter that is not needed.
You can refer to Post numbers as easy.
 
Upvote 0
You're very welcome and thank you for letting us know.
Good Luck
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top