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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
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
 

lookman

New Member
Joined
Oct 19, 2019
Messages
3
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 !
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,460
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,377
Messages
5,414,094
Members
403,515
Latest member
see_mido

This Week's Hot Topics

Top