netwrked
asked on
Using VBA for Excel to automate spreadsheet search and replace functions
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,
88,43234,344,434,4,AAA=#89
88,2313,,454,34,24,324,,6,
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
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,
88,43234,344,434,4,AAA=#89
88,2313,,454,34,24,324,,6,
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
ASKER
Wow, that appears to run great, and much faster (23 minutes versus 164 minutes) then the way we are doing it now. Is it possible to do this with an external workbook containing the static search/replace data? Or, would I be better off writing code to import the sheet to be converted into the static workbook, do the function and then saving the converted worksheet back to a text file. I was not sure if I would take a performance hit by using the external workbook.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
netwrked:
Thank you for the grade and the points. Please let us know if you have any further questions.
Kindest Regards,
Jaes
Thank you for the grade and the points. Please let us know if you have any further questions.
Kindest Regards,
Jaes
Yes this is entirely possible and sounds like it would be much faster than the way you are currently approaching the task. Sheet1 should have your raw data and Sheet2 should have your Search and Replace. Run the following code against it and it should do all the replacements quite rapidly.
Sub SearchReplace()
Dim Cell As Range
Dim ws As Worksheet, wsR As Worksheet
Set ws = Sheets(1)
Set wsR = Sheets(2)
Application.ScreenUpdating
For Each Cell In Intersect(wsR.[A:A], wsR.UsedRange)
ws.Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0
Next Cell
Application.ScreenUpdating
End Sub
Please let me know if you have any questions.
Kindest Regards,
Jaes