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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,201
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,390
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.
 

Forum statistics

Threads
1,077,772
Messages
5,336,150
Members
399,067
Latest member
CJWFM

Some videos you may like

This Week's Hot Topics

Top