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
|