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

[lookman]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Address[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]2nd Street, NY[/TD]
[TD]A1A1 is 35 year old, A1A1 is NewYork ....[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD]4th street, FL[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A1A1 is 35 year old, A1A1 is Florida ....[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD]Manly[/TD]
[TD]5Th street, NY[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A1A1 is 35 year old, A1A1 is NewYork ....[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]A1A1 is 35 year old, A1A1 is NewYork ....[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD]Drake[/TD]
[TD]77th Street, NY[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A1A1 is 35 year old, A1A1 is NewYork ....[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD]Bale[/TD]
[TD][/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A1A1 is 35 year old, A1A1 is NewYork ....[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Address[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]2nd Street, NY[/TD]
[TD]John is 35 Years old, John is NewYork[/TD]
[/TR]
[TR]
[TD]Kent[/TD]
[TD]4th Street, FL[/TD]
[TD]Kent is 35 Years old, Kent is Florida[/TD]
[/TR]
</tbody>[/TABLE]

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]

Hi & welcome to MrExcel.
How about

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]

Hi & welcome to MrExcel.
How about

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]

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.

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.

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.

 

[lookman]

Thanks Very Much Sir the Second Code worked perfectly and fast.

 

[jolivanes]

You're very welcome and thank you for letting us know.
Good Luck