A coworker gets a workbook full of data that he needs for generating metrics every week. The problem is that this workbook is loaded with textual errors. There are leading and trailing spaces all over the place, there are misspellings of all sorts, there are multiple entries that all mean the same thing. He might see one record for “Business Analyst”, and another for “BA”. He might also see an entry for “Busniess Analyst”. The data in this workbook is clearly manually entered, and because my coworker doesn’t own it, he can’t control the cleanup of the data. He has to cleanse it every week in order to extract the metrics he needs.
This week he finally had had enough and asked my help developing a couple of routines to clean up this spreadsheet. The first was to trim all the extra spaces from the data. Easy-peasy. The next was part was to develop a method to find/replace a number of entries, which meant setting up a sort of batch-like utility where he could load up the things he wanted fixed and run them with the touch of a button. This was pretty straightforward, except I, of course, couldn’t stop at just a quick little utility. No. I had to build my own thing that could be used by anyone and had all the normal functionality in the native Find/Replace, except that it would run in a batch-like mode.
I’ll post more about this utility in another few posts, but today’s post was emulating the messaging that Find/Replace gives you when you use the native function. Specifically, I wanted to give the user a total count of the changes made after running the “batch”.
Here’s where I had to put on the ol’ thinking cap. When you use the Find/Replace function in Excel, you get a response that looks something like this:
Okay, great. Exactly what I want to present. However, when you use the Range.Replace method in VBA, you don’t get a count of items replaced as the return for the method. The Range.Replace method returns a boolean. How frickin annoying is that?
So I started thinking what was the best way to capture the total number of items changed? The first one that springs to mind is the good old For/Next loop. Set a range variable, loop through all the cells in that range, check ’em out to see if their contents match your “find” string (which you have doubtlessly captured as part of an input box or something similar), and if the contents match, change them with the “replace” string and increment a counter variable.
Sure. That would work. But I don’t like to iterate through ranges if I don’t have to. Doing so in VBA tends to take a lot of processing time, especially the bigger your range. I really wanted to use the Range.Replace method if I could because it was designed to take on large chunks of ranges in a single statement.
So, how to do it?
I decided on another function that’s designed to take on large ranges in a single statement: COUNTIF. Using Application.WorksheetFunctions.CountIf, I was able to use the range I’m acting upon as the first parameter, and the “find” string as the second. Bracket the “find” string with a couple wildcard “*”s and pass the COUNTIF result to a long variable, and suddenly I had captured the number of changes I’d be making and actually making the changes, all in two lines of code.
Now, the code looks something like this:
Const sWILDCARD As String = "*" Dim sFind As String Dim rReplace As Range Dim lCount As Long sFind = Application.InputBox("Enter the string to be found:") Set rReplace = Selection lCount = Application.WorksheetFunction.CountIf(Rng, sWILDCARD & sFind & sWILDCARD) '...more code here as needed
Two lines of code. It’s a beautiful thing.
More to come on this one as it continues development…
11 thoughts on “Excel Geeking: Counting Instances Of Find/Replace With VBA”
I have a similar problem to your friend. I have to make a lot of find/replace on a worksheet – on a regular basis. The changes are constant in content (I have around 100 find/replace pairs) – and i have a macro command that does the whole thing. I am trying to understand how to get the total amount of replacements (of all the 100 actions) together. I tried to figure it out from your example but couldn’t… I’m a newbie without experience, so I created my macro with the “record macro” function. Is there an option to add some sort of count function to the macro created this way?
No doubt there is. With any recorded macro, it’s just writing code behind the scenes. Once you’ve finished recording the macro, you can go into the recorded code and change any part of it you want. Somewhere in that process, you would insert your count function.
Thanks Scott. I managed to find the coding area, I’m just not sure what the count function should be…
Application.WorksheetFunction.CountIf does not work when numbers (example : 2015) is entered in a cell. It only works with letters / text (example : Apple) – can you please help with a function which will ALSO count the number of cells to change which contains numbers. Thank you.
Have you tried enclosing your numbers in quotes? COUNTIF(YourRange, “2015”) may work (I’m writing this on a phone so I can’t double check it right now…)
Thanks for the snippet. I want to apply your method, however I’m not using a single string for find and replace, but a table range of find and replace values (using Selection.Replace). Would this still work for that setup?
You’d have to modify it a little bit. You’re best/quickest bet would be to keep a running total in the counter variable and each time you us Replace, you add the number of instances found to the variable to get a cumulative total.
I’m wondering if you finally finished this code, I’m looking for something very similar.
What if I want to replace formula with another one? does it give me the count of replacement had made in formula?
Is there any way to capture the Replacement Message from MS office which shows count of Replacement has made (using VBA)?