I am looking for a better method to do search and replace for data on an Excel VBA file import conversion application. Currently, we have a subroutine that performs 4,500 search and replaces. This subroutine runs for each record imported. Generally, there is only one replacement per record.
The import file is a CSV delimited file, but it is not that easy. It has a record structure like:
12,1234,43445,3435,454,45,
,4322,6565
6,3423432,
23232,23,2
3,23,23232
5,5,67,6,6
76
88,43234,344,434,4,AAA=#89
89894,324,
32,432,4,4
3,24,324,3
24,324,36,
7,87,9,88,
755
88,2313,,454,34,24,324,,6,
6,87,78,45
3,4,43,43,
44\TC
Currently, I import each line, looking for the #12 at the beginning. I then grab the next line and look for the AAA=#898989. The 898989 gets replaced with 1234344369765. It then reads the next line, sees the \TC and knows that is the end of the record. This then repeats.
What I would like to do is just open up the file as a block of text, not in columns like it opens now. Then, I would like to be able and put the 4,500 criteria in a table on a separate spreadsheet. First column would contain data to search for and the second column would contain the replacement data. Then, run a search and replace function that reads the first line of the criteria table, use that entry for the search/replace, go to the next line in the criteria table, etc.
It would go something like this:
Search Replace
8989894 1234344369765
1234121 9579842478944
3374622 4509878348240
Routine seaches entire document for 8989894 and replaces all instances with 1234344369765. then, routine seaches entire document for 1234121 and replaces all instances with 9579842478944 and so on. Is this doable?
Thanks,
Joe
Start Free Trial
