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).
<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.
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.