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.

 

[igold]

Hi,

What is the relationship between the old Acct number and the new Acct number.

Are the new Acct numbers the same as the old ones with the addition of a "1" at the end. If not, how do we know which new number matches which old number...

 

[normanbox]

Thank you for the clarification question. The account number changes can be any number of different things; however, there is a fixed location for both the new and old account numbers in the spreadsheet that needs to be found and replaced. The first old account number to find is in C2 and the new account number that will replace the old account number is in E2. Then the second old account number is C3 and the new account number to replace it is in E3. The pattern remains the same for all 270. In other words:

C2 with E2
C3 with E3
C4 with E4
C5 with E5
and so on.

Thank you!

 

[igold]

Does this do what you want... Please test on a backup copy of your data.

Sub newAccts()
    Dim NewAct As Variant
    Dim lrow As Long
    lrow = Cells(Rows.Count, 5).End(xlUp).Row
    NewAct = Range("E2:E" & lrow)
    Range("C2").Resize(UBound(NewAct)) = NewAct
End Sub

 

[Akuini]

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:

Try this:

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 va(i, 1) = vb(j, 1) Then
            va(i, 1) = vb(j, 3)
        End If
    Next
Next
Range("A1").Resize(UBound(va, 1), 1) = va

End Sub

 

[normanbox]

Does this do what you want... Please test on a backup copy of your data.

Sub newAccts()
    Dim NewAct As Variant
    Dim lrow As Long
    lrow = Cells(Rows.Count, 5).End(xlUp).Row
    NewAct = Range("E2:E" & lrow)
    Range("C2").Resize(UBound(NewAct)) = NewAct
End Sub

igold, Unfortunately it doesn't work like I need. The code seems to be more of a copy and paste rather than replace. Also, I need it to replace the account number found in Column A that is listed in Column C and replace it with the "found" account number in column E. I did update the range to A2 instead of C2; however, I missed giving what must be an important detail. The new account number is imbedded between other text of data that must remain. Here's an example if I needed to find Acct_997 and replace it with Acct_957: FS220A_CC.Acct_997 AS [Net Worth] with FS220A_CC.Acct_957 AS [Net Worth].

 

[normanbox]

Try this:

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 va(i, 1) = vb(j, 1) Then
            va(i, 1) = vb(j, 3)
        End If
    Next
Next
Range("A1").Resize(UBound(va, 1), 1) = va

End Sub

Akuini, Unfortunately it doesn't work either like I need. The code replaces the account number in Column A; however, it doesn't replace it when it's surrounded by other text. Here's the same example I gave igold if I needed to find Acct_997 and replace it with Acct_957: FS220A_CC.Acct_997 AS [Net Worth] with FS220A_CC.Acct_957 AS [Net Worth].

 

[Akuini]

Akuini, Unfortunately it doesn't work either like I need. The code replaces the account number in Column A; however, it doesn't replace it when it's surrounded by other text. Here's the same example I gave igold if I needed to find Acct_997 and replace it with Acct_957: FS220A_CC.Acct_997 AS [Net Worth] with FS220A_CC.Acct_957 AS [Net Worth].

OK, try 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) = vb(j, 3)
        End If
    Next
Next
Range("A1").Resize(UBound(va, 1), 1) = va

End Sub

 

[igold]

Perhaps you could provide a before and after graphic representation as opposed to a description of what you would like to happen...

 

[normanbox]

I’m happy to provide a graphic representation.
Before

Column A (Update Needed)	Column C (Old Account Number List)	Column E (New Account Number List
CUData.REGION, CUData.SE, FS220A_CC.Acct_997, FS220_CC.ACCT_047B, …	Acct_997	Acct_957
CUData.REGION, CUData.SE, FS220A_CC.Acct_400, FS220_CC.ACCT_400A,…	Acct_400A	Acct_400B
CUData.REGION, CUData.SE, FS220A_CC.Acct_047E, FS220_CC.ACCT_025B,…	Acct_047E	Acct_047E1

<tbody>
</tbody>

After

Column A (After Update)	Column C (Old Account Number List)	Column E (New Account Number List
CUData.REGION, CUData.SE, FS220A_CC.Acct_957, FS220_CC.ACCT_047B, …	Acct_997	Acct_957
CUData.REGION, CUData.SE, FS220A_CC.Acct_400, FS220_CC.ACCT_400B,…	Acct_400A	Acct_400B
CUData.REGION, CUData.SE, FS220A_CC.Acct_047E1, FS220_CC.ACCT_025B,…	Acct_047E	Acct_047E1

<tbody>
</tbody>

Hopefully this helps.