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…