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

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

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

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

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

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