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
|