Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a massive find & replace macro
I may be in the wrong newsgroup and if so, please direct me to the right one.
Our main database program (non-Excel) creates a file that can be opened in Excel. The file I'm working with has code numbers in it, similar to account numbers. I want to "convert" the account numbers to their actual name. So far, I've accomplished that by doing a manual "find & replace" for each account number (e.g., "find all the occurrances of '12345' and replace it with "General Income"") How could I write a macro to accomplish this for me? I'd like one where I could easily add a new number and description. Any ideas? Thanks in advance. Jerry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a massive find & replace macro
You can just put the macro recorder on and do a search and replace and
look at the generated code and alter it to your requirements. Wouldn't it be better though to update the database itself with an UPDATE query or a number of UPDATE queries? RBS "JWCrosby" wrote in message ... I may be in the wrong newsgroup and if so, please direct me to the right one. Our main database program (non-Excel) creates a file that can be opened in Excel. The file I'm working with has code numbers in it, similar to account numbers. I want to "convert" the account numbers to their actual name. So far, I've accomplished that by doing a manual "find & replace" for each account number (e.g., "find all the occurrances of '12345' and replace it with "General Income"") How could I write a macro to accomplish this for me? I'd like one where I could easily add a new number and description. Any ideas? Thanks in advance. Jerry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a massive find & replace macro
Hi, Jerry. If your names and account numbers don't change much, you might
try creating a separate workbook with the list of numbers in Column A and the corresponding names in Column B. Your macro would then open this book and iterate down the rows, grabbing the text in A as the Find string and the text in B as the Replace string. Editing accounts is as easy as editing your Accounts workbook. (Note: macro done in Excel / Windows XP.) Sub Test_0054() Dim wb1 As Workbook Dim wb2 As Workbook Dim wks1 As Worksheet Dim wks2 As Worksheet Dim rng As Range Dim LastRow As Long Dim x As Long Dim strFind As String Dim strRepl As String Set wb1 = ActiveWorkbook Set wks1 = wb1.ActiveSheet Set rng = wks1.UsedRange Set wb2 = Workbooks.Open("C:\MyAccounts.xls") Set wks2 = wb2.Sheets(1) LastRow = wks2.Range("A65536").End(xlUp).Row For x = 2 To LastRow ' Assumes headers in Row 1 strFind = wks2.Range("A" & x) strRepl = wks2.Range("B" & x) rng.Replace _ What:=strFind, Replacement:=strRepl, _ SearchOrder:=xlByRows, MatchCase:=True Next x End Sub -- Ed Chief Chef, Kludge Code Cafe "Spaghetti Is Our Specialty!" ' "JWCrosby" wrote in message ... I may be in the wrong newsgroup and if so, please direct me to the right one. Our main database program (non-Excel) creates a file that can be opened in Excel. The file I'm working with has code numbers in it, similar to account numbers. I want to "convert" the account numbers to their actual name. So far, I've accomplished that by doing a manual "find & replace" for each account number (e.g., "find all the occurrances of '12345' and replace it with "General Income"") How could I write a macro to accomplish this for me? I'd like one where I could easily add a new number and description. Any ideas? Thanks in advance. Jerry |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a massive find & replace macro
You will need to adapt this to fit your workbook names etc but it will work
if the Data source has the numbers and related names in consecutive cells. Sub SrchandRplc() Dim retval Windows("DataSource.xls").Activate Range("A1").Activate For Each retval In Sheets Do retval = ActiveCell retval2 = ActiveCell.Offset(0, 1) If retval = "" Then Exit For Else Dim value Windows("Generated_Report.xls").Activate Sheets("Sheet1").Select ActiveSheet.Range("A1").Select End If For Each value In Sheets Do If Selection = "" Then Exit For ElseIf Selection < retval Then ActiveCell.Offset(1, 0).Activate ElseIf Selection Like retval Then Selection = retval2 End If Loop Until Selection Like retval Next Windows("DataSource.xls").Activate ActiveCell.Offset(1, 0).Activate Loop While retval "" Next End Sub -- Best wishes, Jim "JWCrosby" wrote: I may be in the wrong newsgroup and if so, please direct me to the right one. Our main database program (non-Excel) creates a file that can be opened in Excel. The file I'm working with has code numbers in it, similar to account numbers. I want to "convert" the account numbers to their actual name. So far, I've accomplished that by doing a manual "find & replace" for each account number (e.g., "find all the occurrances of '12345' and replace it with "General Income"") How could I write a macro to accomplish this for me? I'd like one where I could easily add a new number and description. Any ideas? Thanks in advance. Jerry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a massive find & replace macro
I forgot to add that inserting the line:
Application.Visible = False to the beginning and: Application.Visible = True before the end will make it run somewhat faster. -- Best wishes, Jim "JWCrosby" wrote: I may be in the wrong newsgroup and if so, please direct me to the right one. Our main database program (non-Excel) creates a file that can be opened in Excel. The file I'm working with has code numbers in it, similar to account numbers. I want to "convert" the account numbers to their actual name. So far, I've accomplished that by doing a manual "find & replace" for each account number (e.g., "find all the occurrances of '12345' and replace it with "General Income"") How could I write a macro to accomplish this for me? I'd like one where I could easily add a new number and description. Any ideas? Thanks in advance. Jerry |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a massive find & replace macro
Here is another, more useful, routine for large quantities of data. The idea
is that you would have a separate workbook or, at least, a separate sheet for the routine. Column "A" would hold your names, "B" would hold the respective account numbers. The report would be pasted on Columns "C" through howver many a are in the report with the account numbers in column "D". The procedure will replace the account numbers in "D" with their proper names from "B" as the routine matches numbers. Sub Replacethem() Dim sAddr As String Dim rngA As Range, rngB As Range Dim rng As Range, cell As Range Dim res As Variant With Worksheets("Sheet1") Set rngB = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown)) Set rngC = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown)) For Each cell In rngB Set rng = rngC.Find(cell.Value, _ After:=rngC(rngC.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do rng.Offset(0, 1) = cell.Offset(0, -1).Value Set rng = rngC.FindNext(rng) Loop While rng.Address < sAddr End If Next End Sub -- Best wishes, Jim "JWCrosby" wrote: I may be in the wrong newsgroup and if so, please direct me to the right one. Our main database program (non-Excel) creates a file that can be opened in Excel. The file I'm working with has code numbers in it, similar to account numbers. I want to "convert" the account numbers to their actual name. So far, I've accomplished that by doing a manual "find & replace" for each account number (e.g., "find all the occurrances of '12345' and replace it with "General Income"") How could I write a macro to accomplish this for me? I'd like one where I could easily add a new number and description. Any ideas? Thanks in advance. Jerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Massive Find and Replace | Excel Discussion (Misc queries) | |||
find a specific or several values in a massive datasheet | Excel Worksheet Functions | |||
create a form and find a value in one cell and replace a value in | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
A Macro to Do Find and Replace | Excel Programming |