Find and Replacing Old Account with New Account

[normanbox]

Good morning,
I know this has been answered a number of times already, but I don’t know enough VBA to interpret the code and manipulate it to meet my specific needs. Basically, I need to search column A for old account numbers (listed in column C), and replace the old account numbers with new account numbers (listed in column E). I also have text in columns D and F I use as a feature to track which account numbers have been searched and replaced. Here’s a sample of my structure followed by the macro I created (doesn’t work).

Queries		Old Account	Searched?	New Account	Completed?
QryMBL		ACCT_814E	Yes	ACCT_814E1	Yes
SELECT DISTINCT CUData.REGION, CUData.SE, CUData.DISTRICT AS Dist, CUData.CU_NUMBER AS Charter, CUData.CU_NAME AS Name, CUData.STATE, CUData.CURRENT_NCUA_CAMEL AS CAMEL, CUData.CURRENT_STATE_CAMEL AS [SSA CAMEL], FS220_CC.Cycle_date AS Cycle, FS220_CC.ACCT_010 AS Assets, FS220A_CC.Acct_997 AS [Net Worth], FS220_CC.ACCT_025B AS Loans, [FS220H_CC]![ACCT_400T] AS NMBLB, IIf([Loans]=0,0,([NMBLB]/[Loans])*100) AS [NMBLB to Lns], [FS220H_CC]![ACCT_814E] AS Unfund, [NMBLB]-[Unfund] AS [NMBLB-Unfund], ([NMBLB-Unfund]/[Assets])*100 AS [NMBLB OS to Asts], FS220_PP1!ACCT_400A+FS220_PP1!ACCT_400B AS [Prior NMBLB], IIf([Prior NMBLB]=0,0,([NMBLB]-[Prior NMBLB])/[Prior NMBLB])*100*FS220_CC!AnnFactor AS [NMBLB Growth], [FS220I_CC]![ACCT_041G1]+[FS220I_CC]![ACCT_041G2]+[FS220I_CC]![ACCT_041P1]+[FS220I_CC]![ACCT_041P2] AS [MBL Delq], IIf([NMBLB-Unfund]=0,0,(([MBL Delq]/[NMBLB-Unfund])*100)) AS [MBL Delq Ratio], FS220C_CC!ACCT_550G-FS220C_CC!ACCT_551G+FS220C_CC!ACCT_550H-FS220C_CC!ACCT_551H AS [MBL Net CO], IIf([NMBLB]=0,0,([MBL Net CO]/[NMBLB])*100) AS [MBL CO Ratio], IIf([Assets]=0,0,([NMBLB]/[Assets]*100)) AS [NMBLB to Assets], IIf([Net Worth]=0,0,([NMBLB]/[Net Worth])) AS [NMBLB times NW], IIf([NMBLB to Assets]>12.25 Or [NMBLB times NW]>1.75,"Exceeds Regulatory Limit"," ") AS Risk	ACCT_041G1	Yes	ACCT_041G9	Yes
FROM ((((((CUData INNER JOIN FS220_CC ON CUData.JOIN_NUMBER = FS220_CC.join_number) INNER JOIN FS220A_CC ON (FS220_CC.Cycle_date = FS220A_CC.Cycle_date) AND (FS220_CC.join_number = FS220A_CC.join_number)) INNER JOIN FS220B_CC ON (FS220A_CC.Cycle_date = FS220B_CC.Cycle_date) AND (FS220A_CC.join_number = FS220B_CC.join_number)) INNER JOIN FS220C_CC ON (FS220B_CC.Cycle_date = FS220C_CC.Cycle_date) AND (FS220B_CC.join_number = FS220C_CC.join_number)) INNER JOIN FS220I_CC ON (FS220C_CC.Cycle_date = FS220I_CC.Cycle_date) AND (FS220C_CC.join_number = FS220I_CC.join_number)) INNER JOIN FS220H_CC ON (FS220I_CC.Cycle_date = FS220H_CC.Cycle_date) AND (FS220I_CC.join_number = FS220H_CC.join_number)) INNER JOIN FS220_PP1 ON FS220H_CC.join_number = FS220_PP1.join_number;	ACCT_041G2	Yes	ACCT_041G10	Yes
		ACCT_041P2	ACCT_041P10

<tbody>
</tbody><colgroup><col span="6"></colgroup>

Here’s the code:
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

I tried replacing the “Acct_814E” and “Acct_814E1 with an “Acct_*”, but that didn’t work.

Thank you for your help.

 

[normanbox]

I am rewriting my request because I've had nobody reply so far. I hope this isn't very complex, but I'm afraid I may have made it look scary be providing too much detail. Please let me know if what I need can be accomplished and the solution if you have one.

Help Please,

I know this has been answered a number of times already, but I don’t know enough VBA 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.