Macro to Find and Replace - Speed

[shades]

I had written a macro about two years ago that replaced longer company names with common abbreviations. I store it in Personal.xls, and it has been relatively fast (2-5 sec for most projects with < 2000 rows). However, I tried it on a file with 15,000 rows, and it choked Excel - i.e. it wouldn't run and had to be force quite (Excel XP on Windows 2000).

Here is a portion of the macro (I have about 40 companies that need to be abbreviated, names here have been simplified to protect the guilty ). I stepped through a couple of these lines to see what would happen, and each line took at least 1-2 min.

Sub ChgCompNames()

Application.ScreenUpdating = False
    Cells.Replace What:="Company AAAA", Replacement:="AAAA", LookAt:=xlPart, SearchOrder:=xlByRows
    Cells.Replace What:="Company BBBB", Replacement:="BBBB", LookAt:=xlPart, SearchOrder:=xlByRows
    Cells.Replace What:="Company CCCC", Replacement:="CCCC", LookAt:=xlPart, SearchOrder:=xlByRows
    Cells.Replace What:="Company DDDD", Replacement:="DDDD", LookAt:=xlPart, SearchOrder:=xlByRows
.
'etc. for all company names
.
.
Application.ScreenUpdating = True
End Sub

Is there a better way to go about the replacing? Or is there a way to make this more efficient? (I use this macro just about everyday, and this is the first time that I have run into such a problem.)

 

[shades]

Okay, I just got into work and am surprised - and delighted - to see the additional discussion. I have several meetings today and tomorrow, so will try to make progress on this in between "non-productive meetings".

I think my initial hunch (without any testing) is that the referencing of another Excel sheet for the changes may be the best, rather than hard-coding in VBA. I look forward to this challenge!

Thanks to everyone. I have learned more about VBA, and even more about how to approach a problem to analyze potential solutions.

 

[shades]

Okay, I tried a couple of solutions. Parry, your original took 36 seconds (with 40 company names and 14,786 rows).

Then after reading Tommy's latest (below), I tried it but used all 236 company names, not just the 40 most common ones, on 14,786 rows - 2.05 seconds! And the company names are not sorted on the target column.

Thanks, to everyone. I am going to try others as time allows, but this method for my setup is absolutely great!!

Now, should I have this Data sheet (columns A&B) as a sheet with Personal.xls? So that it would be available for every workbook? Or should I just keep it available to import to a specific spreadsheet each time I need to do this? Then do I put the code in Personal.xls even if I don't put the Data sheet there?

Hi

Sub DictionaryModel()
Dim dctCompany As New Dictionary
Dim rgReplace As Range
Dim vaReplace As Variant
Dim C As Range, start As Long, x As Long, LastRow As Long
Dim IndexCol As Range

Set IndexCol = Application.InputBox(prompt:="Point out the header in the column for replacement", Type:=8)
start = Timer
LastRow = Cells(65536, IndexCol.Column).End(xlUp).Row
Set rgReplace = Range(IndexCol.Offset(1, 0), Cells(LastRow, IndexCol.Column))

For Each C In Sheets("Data").Range("A2:A" & Sheets("data").Range("A65536").End(xlUp).Row)
dctCompany.Add Key:=CStr(C.Value), Item:=CStr(C.Offset(0, 1).Value)
Next

vaReplace = rgReplace

For x = 1 To UBound(vaReplace, 1)
If dctCompany.Exists(vaReplace(x, 1)) = True Then vaReplace(x, 1) = dctCompany.Item(vaReplace(x, 1))
Next

rgReplace = vaReplace

Set dctCompany = Nothing
Set rgReplace = Nothing
Set vaReplace = Nothing
MsgBox Timer - start
End Sub

Wow! I sitll can't get over the speed!

 

[XL-Dennis]

shades,

Yes, it is very fast

Now, should I have this Data sheet (columns A&B) as a sheet with Personal.xls? So that it would be available for every workbook? Or should I just keep it available to import to a specific spreadsheet each time I need to do this? Then do I put the code in Personal.xls even if I don't put the Data sheet there?

If the list does not changes frequently then turn it into an XLA and add a menu-option to the Worksheet Menu bar to run the procedure. Personally I try to avoid the personal.xls file.

Kind regards,
Dennis

 

[parry]

Okay, I tried a couple of solutions. Parry, your original took 36 seconds (with 40 company names and 14,786 rows).

So this was the one with your original code but I just changed the search to one column? That seems dreadfully slow to me as making 30,000 replacements took about 4 secs for me. Mind you that was only over 3 companies (10K each) and the data will probably be quite different. Out of interest did changing the search to 1 column versus what you had originally make any difference at all?

Then after reading Tommy's latest (below), I tried it but used all 236 company names, not just the 40 most common ones, on 14,786 rows - 2.05 seconds! And the company names are not sorted on the target column.

Thanks, to everyone. I am going to try others as time allows, but this method for my setup is absolutely great!!

Thats excellent news and well done Tommy! I had never heard of the Dictionary object before so I must try it. There are other excellent options here as well so when Im in the mood I'll check them all out.

Keep us posted with your findings

I'll let Tommy answer your questions regarding where to store the macro etc.

 

[shades]

So this was the one with your original code but I just changed the search to one column? That seems dreadfully slow to me as making 30,000 replacements took about 4 secs for me. Mind you that was only over 3 companies (10K each) and the data will probably be quite different. Out of interest did changing the search to 1 column versus what you had originally make any difference at all?

:

Yes, I used the 1 column version. The unspecified routine ran about 90 sec, compared to the 1 column of 35 sec, so your modification speeded up my original macro (120+ sec), but slower than the 1 column approach. The target column is not sorted, so maybe that is why it takes much longer. And it may be that each company name is unique, meaning that we don't have "company" in each one.

And, I am working on a 4 year old Toshiba 8100 laptop (600 MHz) so I don't hav eth ebig horsepower to process instense work. Given Tommy's solution, even on this older machine it works so quickly.

 

[parry]

Interesting, so reducing the search to 1 column made quite a significant difference. Thats good to know. However, not a patch on Tommy's solution.

Thanks for the info.

 

[shades]

Okay slight problem. I put the macro from Tommy's solution into a file and made it an .xla (add-in file). Then added it via Tools > Add-ins. I did some experimenting with developing an add- (.xla file). Changed my mind and took it out (removed it from the add-in dialog box, and removed it from the add-in folder). However, when I restarted Excel then this warning appeared.

"File Not Found"

Now I am running into a problem. Every time I open Excel, a warning comes up "File Not Found". When I click okay, Excel finishes loading and works fine. I clicked on the Help button once and received this:


quote:
--------------------------------------------------------------------------------
The file was not found where specified. This error has the following causes and solutions:

A statement, for example, Kill, Name, or Open, refers to a file that doesn't exist.
Check the spelling of the file name and the path specification.

An attempt has been made to call a procedure in a dynamic-link library (DLL) or Macintosh code resource, but the library or resource file name specified in the Lib clause of the Declare statement can't be found.
Check the spelling of the file name and the path specification.

In the development environment, this error occurs if you attempt to open a project or load a text file that doesn't exist.
Check the spelling of the project name or file name and the path specification.
--------------------------------------------------------------------------------



Anyway I unloaded all add-ins, and removed all but two VBA References, and the warning still occurs.

Then I thought maybe I should put this macro back into Personal.xls. But then even curiouser...

Now when I start up, a dialog box comes up saying:

quote:
--------------------------------------------------------------------------------

Runtime Error 1004
Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command.
--------------------------------------------------------------------------------



The only hidden workbook I have is Personal.xls. But I can't find where it's editing or needs to edit.

After I click to dismiss the box, then the other warning box comes up "File not found."


---------------------

Now when I take the macro above from Tommy out of Personal.xls, then the "edit macro" warning does not show up, but the "File not found" warning still does.

Any ideas?

 

[Tommy Bak]

Shades..
I belive that you have deleted the add-inn. Thats ok.
Now check if it still can be seen in add-inn's box. If it can, then checkmark it. Excel will then tell you that it cannot find it you and ask if it should delete the reference to the add-inn. Your answer should be YES.
Now this problem is solved

I wrote that I would place the code and the repacementlists in Persona.xls.
I take that back (I just saw my own and it's huge and filled with seldom used code)

Instead I will suggest that you place both in a new and clean workbook and save it.
Then, when you have to do the job, open it and run the makro. This way you don't have to carry this in the computers memory all the time you're using excel.

The code should be modified a little to do that. (se below)
The code shall then always be started when the sheet that needs replacement is the active one.

Sub DictionaryModel()
Dim dctCompany As New Dictionary
Dim rgReplace As Range
Dim vaReplace As Variant
Dim C As Range, x As Long, LastRow As Long
Dim IndexCol As Range

Set IndexCol = Application.InputBox(prompt:="Point out the header in the column for replacement", Type:=8)
LastRow = Cells(65536, IndexCol.Column).End(xlUp).Row
Set rgReplace = Range(IndexCol.Offset(1, 0), Cells(LastRow, IndexCol.Column))

With ThisWorkbook.Sheets("Data")
    For Each C In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
        dctCompany.Add Key:=CStr(C.Value), Item:=CStr(C.Offset(0, 1).Value)
    Next
End With

vaReplace = rgReplace

For x = 1 To UBound(vaReplace, 1)
     If dctCompany.Exists(vaReplace(x, 1)) = True Then vaReplace(x, 1) = dctCompany.Item(vaReplace(x, 1))
Next

rgReplace = vaReplace

Set dctCompany = Nothing
Set rgReplace = Nothing
Set vaReplace = Nothing
End Sub

best regards
Tommy

 

[shades]

Shades..
I belive that you have deleted the add-inn. Thats ok.
Now check if it still can be seen in add-inn's box. If it can, then checkmark it. Excel will then tell you that it cannot find it you and ask if it should delete the reference to the add-inn. Your answer should be YES.
Now this problem is solved

Not quite. I have followed that procedure before and it worked great.

Not so this time. Also, the warning "File Not Found" is a Visual Basic message that appears after the add-ins are loaded but before Excel finishes loading.

 

[Legacy 21301]

Shades

Would be interested in how long the macro I posted takes (based on using VLOOKUP).

Thanks.