Find and Replace List

[normanbox]

Help Please,

I know this has been answered a number of times already, but I don’t know enough <acronym title="visual basic for applications">VBA</acronym> to interpret the code and manipulate it to meet my specific needs. Basically, I need to update old account information with new account information by doing a find and replace. The problem is, I have a bunch of items I need to ‘replace’, so I’d like a macro with a loop to search for old account numbers (the list of account numbers to search is listed in column C) that are in Column A and replace the old account numbers in Column A with the new account numbers found in Column E. There are 270 account numbers to search. Here’s the macro I have; however, it doesn’t work:

Sub FindReplace()
'
' FindReplace Macro
'

'
Range("D1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.Copy
Range("F1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Columns("A:A").Select
Selection.Replace What:="Acct_814E", Replacement:="Acct_814E1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("D1").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
End Sub

Let me know if you have any questions.

Thank you.

 

[Akuini]

I’m happy to provide a graphic representation.

Hopefully this helps.

Ok, this one:

Sub a1012562a()
'https://www.mrexcel.com/forum/excel-questions/1012562-find-replace-list.html
Dim va, vb
Dim i As Long, j As Long
va = Range("A1", Cells(Rows.count, "A").End(xlUp))
vb = Range("C1", Cells(Rows.count, "E").End(xlUp))


For i = 1 To UBound(va, 1)
    For j = 1 To UBound(vb, 1)
        If InStr(va(i, 1), vb(j, 1)) Then
         va(i, 1) = Replace(va(i, 1), vb(j, 1), vb(j, 3))
        End If
    Next
Next
Range("A1").Resize(UBound(va, 1), 1) = va

End Sub

 

[normanbox]

That appears to be working great! Thank you very much!

 

[Akuini]

That appears to be working great! Thank you very much!

You're welcome & thanks for replying

 

[normanbox]

Is there a way to make it so the find and replace is not case sensitive? The code works perfectly as long as the case is the same.

Thanks.

 

[Akuini]

Is there a way to make it so the find and replace is not case sensitive? The code works perfectly as long as the case is the same.

Thanks.

OK, try this:

Sub a1012562b()
'https://www.mrexcel.com/forum/excel-questions/1012562-find-replace-list.html
Dim va, vb
Dim i As Long, j As Long
va = Range("A1", Cells(Rows.count, "A").End(xlUp))
vb = Range("C1", Cells(Rows.count, "E").End(xlUp))


For i = 1 To UBound(va, 1)
    For j = 1 To UBound(vb, 1)
        If InStr(1, va(i, 1), vb(j, 1), 1) Then               'case insensitive
         va(i, 1) = Replace(va(i, 1), vb(j, 1), vb(j, 3), , , 1)  'case insensitive
        End If
    Next
Next
Range("A1").Resize(UBound(va, 1), 1) = va

End Sub

 

[normanbox]

Maybe one day I'll be able to do what you do! Once again, it's working perfectly.

Thank you

 

[Akuini]

Maybe one day I'll be able to do what you do! Once again, it's working perfectly.

Thank you

You're welcome & thanks for replying