The Maffetone Method – 5 Months In

Okay, I realize the month’s not over yet, as it is still the last day of February. But I can give you my solemn word that I will not be going for a run between now and midnight tonight, so I think the data is safe to review.

So how did February go? I guess if sum it up by calling it a mixed bag.

Let’s start with the graphics:

running month 5

maffetone_avg_month5

By the list of stats, you can see that I dropped another thirteen seconds off my pace time. Which is great. That’s the whole idea. Keep running slow and eventually the pace comes down to meet your heart rate without your heart rate having to rise to the occasion. Except my heart rate did rise a little bit, on average. More on that in a minute.

The runs themselves were really good through the first half of the month. I ran my first long run in a while, getting out for a good 8 miles on a Sunday morning. I realize 8 miles is not a long run to a lot of “serious” runners, but for me, it was. I hadn’t run a good long distance run since late last summer, and even when I was getting out more regularly, my longest run ever was 12 miles. Since my goal is to run a half marathon in June, I got start cranking up the mileage to make sure I’ll cross the finish line, rather than be carried over it.

So through the first two weeks of Feb, I was feeling good, running 5 to 6 miles without a care in the world. And then it snowed. And not just snowed, because that’s already happened plenty this winter. No, I mean Snowed with a capital S. In the third week in February we saw four snowstorms over an eight day period. I mean, honest to god, really? That threw a huge monkey in the wrench in terms of aerobic base training. One of Maffetone’s primary recommendations is to train slowly all winter long so that you can race in the spring and summer. Do your aerobic training in winter so your spring/summer racing can serve as your anaerobic training. ‘Cept how do you train during the winter when winter won’t let you?

This is where the treadmill at the local gym should have come in. Shoulda woulda coulda. I should have altered my morning routine to go run on the treadmill to keep the training going. But I hate treadmills. They bore me to tears. Guess I wasn’t all that committed after all.

On top of the Snow with a capital S, it’s been cold. Damn cold. Like, Cormac McCarthy’s The Road kinda cold.

So the last two weeks of February saw just two runs for a total of 8 miles. Pretty anemic. But winter was only half the problem I ran into. (Get it? “Ran” into? Never mind…)

My heart rate monitor has been acting fairly wonky lately. I work in IT and I can tell you that “wonky” is a very technical term. Basically, it would give me strange readings as I ran, usually only for a moment, and return to normal. For example, I looked down at it as I leveled out on one particular street during a morning run. I watched it go from 137 to 213 then back down to 137. Whiskey tango foxtrot. And the monitor doesn’t know enough to think “hmm, that’s strange, maybe that’s a blip we should throw out.” When the run is over and I check the average heart rate and max rate, sure enough, the max reads 213. That does me no good. I have to spend the rest of the run closely monitoring the readings and estimating from what I saw what I think the max heart rate was. God only knows what this is doing to the average. The primary concern I have with this is, how do I know any of the readings have ever been right? I’ve replaced the batteries, but it still happens here and there. I really want to get a new monitor, but the
Magellan Echo is still a little out of my price range.

Also on the subject of heart rate, I feel like I’ve lost my feel for pace. The last three runs I’ve run, my average heart rate has been 144. I can usually keep it to 141, 142. My pace for the first two of these runs saw my pace lower that the monthly average by 15 seconds. I thought maybe I was losing conditioning. The last of these three runs I watched my heart rate climb up into that 144 arena. “Here we go again,” I thought. So I (stupidly) cute my run short out of frustration. Three miles and done. I looked at the pace for that run and found I had run it 10 seconds under the average for February. I could have, and would have, sworn I was running a pace like the first two. So I feel I’ve lost my feeling for pace, which is a little bothersome. And to bring in my other issue, how much of this due to a strangely functioning heart rate monitor versus simply losing my feeling for pace. No earthly idea.

So that’s how month five went. I figure winter has to clear out of here at some point. There’s a 10k north of me that happens at the end of April that thinking about. It would be a nice way to get into a racing frame of mind for the June half marathon. But first, I should probably get through March.

Advertisements

Excel Geeking: My Picks For Getting Started With Programming Excel

I’ve fielded this question a couple of times, so I thought I’d give to formal list here. These are the books I’ve used (and continue to) to get started with VBA/Excel programming (among other things). I also list the sites I visit regularly when I feel like expanding my Excel/VBA knowledge.

BOOKS

power programming

Excel 2010 Power Programming with VBA, by John Walkenbach – After I finished poking around Google trying to find the answers to my questions, I decided it was time to get serious and get some structured learning. This was the first book I bought, and remains one of the two I refer to the most.

ped

Professional Excel Development, by Bullen, Bovey, and Green – This is the other book I hit on a regular basis. I never fail to learn something when I go to it (usually something I’ve learned before and had simply forgotten). But take note: this puppy is NOT for beginners. It’s barely for the intermediate programmers. There is a gold mine of information in there, but you really need to know what you’re doing in VBA to digest some of the content. But if you’re ready for it, you can’t do better.

excel prog ref

Excel 2002 VBA Programmers Reference, by Bovey and Rosenberg – This one’s an old one, and has probably been updated since I bought it. But this is the one I’ve got. It has some nice stuff on connecting Excel to Access using both ADO and DAO, including how to connect to Excel as a data source with ADO, which I’ve referenced quite a bit.

prog excel

Programming Excel with VBA and .NET, by Webb and Saunders – I like this one because it’s essentially a paper version of the Excel object model, but instead of being just a straight list of objects, properties, and methods, it gives some textual description of said objects, properties, and methods, and usually gives some nice short code examples of how to use them.

vba dev hnbk

VBA Developer’s Handbook, 2nd Edition, by Getz and Gilbert – This one goes DEEP. Mike is Product Manager with Microsoft’s Developer Tools division, specializing in VBA. He knows this stuff inside out and upside down. The result is some incredibly deep knowledge that can be very useful, but can also go over your head. But it contains things you won’t find in a lot of places. You want to program media files? This is the book. Harness the information about your monitor? It’s in there. Build custom data structures? Yup. It’s all in there. But this this level of programming is pretty complicated, so tread carefully.

pvt tbl

Pivot Table Data Crunching, by Jelen and Alexander – I love this one. It’s a compact book for a computer book (only 288 pages), but it gives so much great info on pivot tables. And not just pivot tables from a user interface perspective, but also includes how to program them with VBA.

sql

Sams Teach Yourself SQL in 10 Minutes, by Ben Forta – This one isn’t a VBA book (as you can clearly see by the name). This one is a great, slim little gem that gives you the basics of how to program SQL. Syntax is everything. And since there will come a point where you have to hook an Excel sheet up to and Access database (don’t shake your head at me, I’m telling you, it WILL happen), this gives you the fundamentals you need to keep from screwing up the syntax.

WEBSITES

Really there are only three sites I hit up on a regular basis.

Daily Dose Of Excel – Dick has been blogging about Excel for at least ten years. He’s got an easy conversational style that’s fun to read and easy to digest. I landed on this site a number of times while searching for VBA solutions I couldn’t figure out on my own. There’s one thing about Dick’s site that makes it unique: it’s the site at which everybody gathers to talk about Excel and VBA. Daily Dose of Excel is not always updated in a timely manner, with sometimes a month between entries (Dick, like most of the rest of us, has a day job), and it’s not like the content is organized into neat categories with each topic covered and cataloged. You can navigate the categories dropdown list in the blog’s sidebar, but don’t expect a page listing all of the topics in an index-like fashion. And yet, every Big Name Excel Personality flocks to it. I don’t know why, but there it is. Daily Dose Of Excel is the Cheers of Excel blogs, and Dick is Sam “Mayday” Malone.

Contextures – This is Deb Dalgleish’s site. It is loaded with content, updated nearly every day. Deb also includes a weekly round up of other Excel related items she’s found on other sites throughout the week. Her content is categorized, with each category giving you examples and tips and tricks and sometimes even quirks to watch out for. To give you an example, I had a column in a sheet two days ago with broken data validation. You could type any old thing in the cells when you should have been limited to a specific list. I figured there had to be a way to fix it. I remembered Deb had a ton of content on data validation on her site, so I went digging. Sure enough, I found what I was looking for and fixed it. But if that’s not enough to convince you, let me add that Deb might have the largest collection of multimedia, primarily videos demonstrating how to implement some of the content she covers, of any Excel site I’ve ever seen.

Pearson Software Consulting – Chip Pearson’s site is a fantastic collection of explanations on all kinds on Excel and VBA topics. But what really makes the content pop is that Chip usually adds custom routines the make your programming easier. For example, you might need to do some work with Names in Excel. Chip walks through the aspects of to code with Names, and then puts the things he’s just discussed together into prepackaged functions that you can call to make your programming life easier. I’ve used his site as a reference point for years.

So that’s it. Those are my recommendations. Have fun.

http://www.youtube.com/watch?v=7KtAgAMzaeg

Pictures? We Don’t Need No Stinkin Pictures!

I’ve seen a couple of articles recently about the next generation’a digital presence. Some are best practices, some are just downright alarmist. Take this article from Slate, which feels like it’s verging on paranoid. “Facial recognition”, “corporate data mining”, these things are described in an almost Orwellian manner.

Maybe I’m being naive about Facebook and how the pictures I upload will be used in the future. Perhaps the Peoples Republic of Facebook is only a few years away. But I doubt it. The fact is, I like sharing pictures of my kids on Facebook so my friends can see them. Posting pictures to Facebook has become the digital replacement for the proud father pulling out his wallet to show you his little girl in her ballet costume. I’ve been loading pictures onto Facebook for years. Yes, my girls will probably moan and groan when they are old enough that I can share photos with their boyfriends. Again we see the digital replacement for going through old photo albums.

However, I’m not so naive to think posting pictures here there and everywhere is a good or safe thing. Because it is not. I share my pictures on Facebook where my friends can see them, and on Instagram, because my account is private and you need my permission to see my content. Where do I not share my pictures? Twitter, Tumblr, and this blog.

I can’t control who views this blog. I could technically make my user account on Twitter private, but that kind of negates the fun of Twitter. But this blog is open for anyone to read. And, like Twitter, that’s the fun of blogging. Creating an online space where you can record your thoughts, feelings, activities, likes and dislikes, all free and open for the entire online world to view.

With my content available to all the world, why would I want to post pictures of my kids?

Cause let’s face it: there are whackos out there. The odds of one lone whacko stumbling across pictures of my kids and seeking them out are probably the equivalent of being struck by lightning while being eaten by a shark all the while holding the winning Powerball ticket. And this scenario (the whacko one, not the lightning/shark/Powerball one) is definitely worst case scenario kind of stuff. But it still gives me a measure of comfort knowing I’m keeping people I don’t know from viewing my kids. I have friends who post pictures of their kids on Twitter and I cringe every time. But that’s my gut reaction. Yours might be different.

Nobody really knows what’s going to happen when the current youth generation comes of age in a socially media driven world. Facebook is only ten years old. What happens when my kids hit the age of consent for social media sites and sign up and see they’ve already been all over the site for ten or fifteen years? Nobody really knows. In the interim, I’ll keep sharing pictures with friends and keeping them away from strangers. My hope is that this will both keep them safe while I share their fun moments, and keep me from being a digitally helicoptering parent.

Excel Geeking: Finding Out If A Range Intersects With PivotCache Source Date

As I was developing a utility to help clean up text for a co-worker, I ran into a error I have been since unable to replicate. Basically I could not get the native Find/Replace function in Excel to actually find and replace items if the range being modified was part of the source data for a pivot table.

So I embarked on a quest to determine if a selected range intersects with a pivot table’s data source, which is otherwise know as a pivot cache.

This should have been a lot simpler than it was. The primary issue is that, while you can read the SourceData property for a pivot cache into a variable, the property returns a string. Which means parsing the string, breaking it into its two parts (the worksheet name and the range address), converting the range address from R1C1 to A1 (since the SourceData property spits out as a R1C1 format), then setting the range variables and seeing if they intersect. Feels like a lot of work just to see if two ranges interset, but there it is. If there’s a better way to do it, I’m all ears.

The final routine is below. It’s set up as a sub, but with a couple of easy modifications it could (and probably should) be converted to a function returning a boolean. But I’ll leave that for you to do. Why should I have all the fun?

Here it is:

Private Sub DoesPivotCacheIntersect()
‘ Description:  This iterates through all the pivot caches in a workbook
‘               and determines whether the selected range intersects with them.

‘ Variable declarations.
Dim wb As Workbook
Dim sh As Worksheet
Dim pvtCache As PivotCache
Dim rSelection As Range
Dim rPivotData As Range
Dim lCnt As Long
Dim sPvtCache As String
Dim sShName As String
Dim sRngName As String

‘ Start by setting one range variable to our selection.
Set rSelection = Selection
‘ Also set the wb to the active workbook on which we’re using the utility.
Set wb = ActiveWorkbook

‘ Iterate through all the pivot caches in the workbook.
For Each pvtCache In wb.PivotCaches

‘ Set the string variable to the SourceData property value.
sPvtCache = pvtCache.SourceData

‘ We need to parse the source data to separate the sheet name from
‘ the range name. We’ll parse backwards, since a sheet name can contain
‘ and exclamation point, and an R1C1 range string cannot.
For lCnt = Len(sPvtCache) To 1 Step -1
If Mid(sPvtCache, lCnt, 1) = “!” Then
sShName = Left(sPvtCache, lCnt – 1)
sRngName = Right(sPvtCache, Len(sPvtCache) – lCnt)
Exit For
End If
Next lCnt

‘ If the sheet name is bracketed by apostrophes,
‘ then we need to trim them or else we’ll get an error
‘ we we try to set the range variable.
If Left(sShName, 1) = “‘” And Right(sShName, 1) = “‘” Then
sShName = Left(sShName, Len(sShName) – 1)
sShName = Right(sShName, Len(sShName) – 1)
End If

‘ We need to convert the R1C1 range that we get from the SourceData property.
‘ To do this, we’ll use ConvertFormula.
‘ We need to add an equal sign to make ConvertFormula think we are dealing
‘ with an actual formula.
sRngName = Application.ConvertFormula(“=” & sRngName, xlR1C1, xlA1)
‘ Once we’ve converted the range string to A1, we remove the equal sign.
sRngName = Replace(sRngName, “=”, “”)

‘ Finally, after much ado, set the range variable
Set rPivotData = wb.Worksheets(sShName).Range(sRngName)

‘ Now let’s see if we have an intersection.
If Not Intersect(rSelection, rPivotData) Is Nothing Then
MsgBox “Your selection intersects with a pivot cache in the active workbook.”, vbOKOnly
End If

Next pvtCache

End Sub

Editing My Fiction (or, What I Should Be Doing Instead Of Watching TV)

I don’t like editing. Like, really don’t like it.

20140217-154120.jpg

Editing to me has always be one of those things I put off and put off and put off (and put off) until I finally run out of excuses. Then I scrounge around under the couch, certain there must be some old, partially eaten, mold-covered excuses I can use until I can get to the store and buy some more.

I’m a world class procrastinator, and when it comes to things I don’t like, I never fail to put off for decades what I can do today. But that just doesn’t fly when it comes to publishing.

I’ve written three books. One I’ve edited to the point where I’m now publishing it on Amazon. The other two are in draft mode. The picture you see at the top of this post is the first two pages of the second book I wrote (and finished–given the number of abandoned writing projects on my computer, it feels important to add that the the first drafts were completed). It is still in first draft status and has been for about seven months or so. I’ve red-penned the first twenty-six pages out of two hundred plus pages. I’ve got a ways to go.

I started editing this second book, “Dirt”, about four weeks ago. It was immediately after I finished the first draft of my third book. I was on a roll and didn’t want the mojo to vanish, didn’t want to slow the momentum [Scott looks around his desk for another tired metaphor and, failing to find one, moves on]. I wanted to continue on and prove to myself that I could conquer the Editing Beast.

Yeah.

No.

The Editing Beast was definitely not a windmill. It broke my lance, killed my horse, and ripped through my armor to tear out my still-beating determination with its fearsome jaws. This sumbitch tore me apart.

I have done just about everything to avoid editing. There has been food, and running, and reading. There has been TV. Justified is back on (woohoo!) as is The Walking Dead (meh). I have discovered Longmire, the whole first season of which I took in like a python unhinging its jaw to swallow a goat. In short, I’ve done everything I can to avoid “Dirt”. And here’s the secret as to why [leans in to whisper]: I don’t like “Dirt”.

It’s true. I shake my head in disbelief at my own feelings about my own work, but it’s true. I don’t like it. “Dirt” is the most Stephen King-like thing I’ve ever written, long or short. It just had that sort of vibe to it. And I dig Stephen King. One hundred years from now, when people ask which author is the most remembered author of the twentith century, it will be Stephen King, just like it is Charles Dicken for the nineteenth century. He’s not writing high fiction, some of his stuff is (by his own admission) a “clunker”. But he tells a helluva story. When I was commuting 120 miles roundtrip every day for work (barefoot, through the snow, without a coat) I would listen to King on audiobook. Since his one goal for so long was to scare the crap out of you, he was great to take on long car rides. Guaranteed to keep you awake. King was one of the first adult authors I remember reading as I grew older.

So it naturally follows that a novel I’ve written with a King-esque feel to it would completely jazz me, right? Wrong. “Dirt” started off as a short story that couldn’t find an ending. The cast of characters grew and grew. Then the primary antagonist introduced himself and I stepped back and asked the book “Really? Is this REALLY where we’re going?” The book nodded emphatically and then dug its heels in as I tried to drag it acros the finish line like a dog going to the vet. It was a rock fight to get this thing done.

Now I’m staring down the editing barrel and asking myself how much do I really want to tackle it. There is so much work to do. Like, a dump truck’s worth of manure to shovel through to find a lost engagement ring. And I’m asking myself, how badly do I want to work on it. Especially since I have another book, my third, that I really did enjoy writing and that I’m looking forward to editing. I think I started doing some editing on “Dirt” because I knew I’d have to abandon it in March when I get to work on scrubbing the hard-boiled detective book. So if I didn’t get around to doing anything on “Dirt”, well does anybody really care?

Unfortunately, yes, I do care. I’m not happy with it, but do care quite a bit. I want to see it finished, I want to see it to completion. I want to see it improved and published and enjoyed. So it might be time to stop scouring the house for excuses, putting it off, and get down to some serious work. Which I’ll do.

I hearby make a solemn vow to stop procastinating over editing “Dirt” and to begin the serious work on getting it into publishable shape.

Right after I edit the other book.

Review: “The Cold Dish” by Craig Johnson, and A&E’s “Longmire”

Today I’m feelin’ generous, so it’s a two-fer. Two reviews in one post. Lucky you! I recently read Craig Johnson’s debut mystery novel “The Cold Dish“, which introduced his character Walter Longmire to readers everywhere. After finishing the book, I powered through the first season of A&E’s original series “Longmire“, which is based on the characters in the Johnson novels.

cold dish

Let’s start with the book. “The Cold Dish” introduces us to Sheriff Walt Longmire, Sheriff of Absaroka County in Wymoing. He’s an aging sheriff, looking forward to a retirement that doesn’t appear too far off. He’s a widower, having lost his wife to cancer about four years back, and has, in many ways, lost his zest for life. Longmire is not the first hero of hard-boiled mysteries to be introduced to the reading public as a cynical, drinking, down-on-life-and-maybe-himself character. Nor will he, doubtlessly, be the last. But he has a voice all his own, part of which comes from the proximity to the sweeping landscapes of the American West. Longmire is a man who knows himself, knows his limits, and is cautious about pushing them.

But push them he does when the situation calls for it. And in “Cold Dish” the situation calls for it quickly. Walt is called to the scene of a crime where the body of a young man is found, shot through the back. The complication here that Walt must deal with is the fact that this deceased young man is one of four that were acquitted of raping a Native American girl with fetal alcohol syndrome a couple years back. It’s a perfect, though perhaps cliched set-up for Walt Longmire to show us what he’s got. Joining him on this venture is a cast of characters that are fairly well-drawn, even if one or two of them ar characitures. There is the female deputy Victoria “Vic” Moretti, a tough-as-nails, unwilling transplant cop from Philly; there is Henry Standing Bear, Walt’s long-time friend and connection to the Native American community on and off the reservation; there is Ruby, the dispatcher and Sheriff’s Office manager who keeps the office moving along while giving Walt stern matronly glares whenever appropriate.

I found “The Cold Dish” a fun read that kept me reading, which is saying a lot these days. At approximately 400 pages, it’s too long, and suffers from a certain laziness in the editing process. I’m used to the sharp crisp sentences of Robert B Parker, and if you’re looking for those here, you’ll be disappointed. There is also a section that starts off as spiritual and quickly borders on supernatural as Walt fights his way up a mountain through a blizzard. It reminded me of the Halloween episode of [insert cop show name here] where the good guys are plagues at every turn by events that seem other-worldly to them, but turn out to be completely plausiable.

But not to leave you thinking I didn’t like the book, because I did. The scenery is well-described so you get a strong sense of the Wyoming countryside. In addition, Johnson, a resident of Ucross, Wyoming, (pop. 25)  gives us insight into the relations between Native Americans (who are called and call themselves “Indians”) and the white population living outside of the “Rez”. These are insights that, coming from a different writer, would feel forced, but never once do they feel so with Johnson at the wheel.

I’m definitely looking forward to the next in the Walt Longmire series. So much so that I decided I had to check out the TV series “Longmire”.

And how is “Longmire”? So glad you asked.

20140216-213327.jpg

As a cop show, it’s average. The plots are standard murder plots, with the usual number of twists and somersaults built in in order to keep you guessing until the end. One of these days, somebody is going to come up with a cop show where you know whodunit, and you (and the detectives) spend the rest of the episode trying to get enough evidence to convict. That’s not this show. There is also, not unlike a lot of shows these days, and over-arching plot that slowly threads its way through each episode and ties itself together in the season finale. The season ends on a sort-of cliffhanger. There’s clearly a lot more story to develop with the characters, but you’re not left wondering who shot JR.

But this is no different than any other cop show these days, which leaves the question, why watch it? The answer: Robert Taylor. Taylor is an Aussie actor who American audiences will only recognize as Agent Jones from “The Matrix”.

untitledrobert-taylor-longmire-4

This role couldn’t be farther from that, and Taylor makes the character of Walt his own. His widowership is only a year (whereas in teh books it’s four years) which makes the pain much closer. He’s aided by Katee Sackhoff in the role of Vic, and she too makes the role her own. Described in the books as a handsome not pretty, square-jawed woman, Sackhoff’s features seem cut to order for the role. Add to that her ability to play hard-nosed, characters (see “Battlestar Galactica” for reference) and she is perfect in the role. Rounding out the cast is Lou Diamond Phillips in the role of Henry Standing Bear. Phillips is not the first person who comes to mind when you think of a bar-owning native American friend of Walt, especially since they are approximately the same age. The first person I thought of for the role was Wes Studi, an excellent Native American actor (see “Last of the Mohicans” for some amazing work). But Phillips, like Taylor and Sackhoff inhabits the role and you never once question whether he belongs there.

I really enjoyed the first season of “Longmire”, for as run-of-the-mill as the murder plots are, the characters are fun to watch and the scenery, well, it can’t be beat.

So there you have it. “The Cold Dish” by Craig Johnson, and “Longmire” the show inspired by Johnson’s characters. I’d recommend both.

Excel Geeking: Counting Instances Of Find/Replace With VBA

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:

find replace count

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…