How Can I Replace Numeric Values in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! We have a spreadsheet where people enter a bunch of data. In order for this data to be used in another application, none of the values can exceed 999; if we have a value like 1234, that’s supposed to be entered as 999, the maximum-allowed value. However, people have a tendency to enter the value as 1234; that means that we can’t import this spreadsheet into our other application until we find this illegal value and change it to 999. How can I write a spreadsheet that will do this fix-up work for me?

— LK

Hey, LK. You know, in addition to taking a vow of poverty, the Scripting Guy who writes this column has also vowed to help anyone who asks him for help. (OK, so, technically, the Scripting Guy who writes this column didn’t actually take a vow of poverty; instead, he sort of had poverty forced upon him. That’s what happens when you have a 17-year-old son, especially one who plays baseball.) Because of his latter vow, the Scripting Guy who writes this column will try to help you; to be honest, however, he’s a little concerned about anyone who comes to him requesting help with “fix-up work.” Fix-up work?!? You obviously haven’t seen the Scripting House lately, have you?

At any rate, fix-up work is definitely not what the Scripting Guy who writes this column does best. But maybe that isn’t his fault. Instead, maybe that’s because there isn’t much need around the Scripting House for a script that goes through an Excel spreadsheet and changes any cell values greater than 999 to the maximum-allowed value of, well, 999:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
For Each objCell in objRange
    If IsNumeric(objCell.Value) Then
        If objCell.Value > 999 Then
            objCell.Value = 999
        End If
    End If
Next

As you can see, there really isn’t much to this script; as it turns out, it’s surprisingly easy to zip through a spreadsheet, find any values that are greater than 999, and then change those values to 999. For starters, we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see on screen. We then use these two lines of code to open the file C:\Scripts\Test.xls, and to bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

Needless to say, there’s nothing too terribly hard about any of that.

Of course, we know what you’re thinking: “Sure, there’s nothing terribly hard about any of that. But now we have to figure out how to do a search-and-replace operation that can find – and then replace – any values greater than 999. How hard is that going to be?”

Well, to tell you the truth, that might be very hard; we don’t know for sure. That’s because we aren’t going to do a true search-and-replace operation (that is, we aren’t going to use Excel’s Replace method). Although we might (or might not) be able to use the Replace method to find and replace these values, we opted for a much easier approach: we’re simply going to grab a collection of all the cells in the worksheet, check the value of each cell individually, and then, if needed, replace that value with 999. As you’re about to see, there’s nothing too terribly hard about any of that, either.

What’s that? You don’t believe us? You still think that this search and replace thing is going to be hard? Let’s see if we can put your mind at ease by explaining exactly how this all works.

To begin with, we use this line of code and the UsedRange property to grab a collection of all the cells in the worksheet. Nothing too terribly hard there:

Set objRange = objWorksheet.UsedRange

When you create an instance of Excel’s Range object (which is what happens when we reference the UsedRange property) we get back a collection of all the cells in that range. In turn, that means we can access each of the individual cells in the range simply by setting up a For Each loop to walk through all the items (cells) in the collection. And – surprise, surprise – that’s exactly what we do next:

For Each objCell in objRange

Inside this For Each loop we do a couple of things. First, we need to determine whether the value in a given cell is a numeric value; if it’s not, we’re going to run into problems (i.e., script-crashing problems) when we try to figure out if that value is greater than 999. Therefore, to avoid problems, we use VBScript’s IsNumeric function to determine whether or not the value in the first cell happens to be a number:

If IsNumeric(objCell.Value) Then

If IsNumeric returns False that means we’re dealing with a string, a date, or some other non-numeric piece of information; in that case, we return to the top of the loop and repeat the process with the next cell in the collection. Let’s assume, however, that IsNumeric returns True; that means that we are dealing with a numeric value. In that case, we then use this line of code to determine if that value is greater than 999:

If objCell.Value > 999 Then

And what if the value is greater than 999? No problem; we simply assign a new value (999) to the cell:

objCell.Value = 999

From there we go back to the top of the loop and try again with the next cell in the collection. By the time we’ve looped through the entire collection of cells any values greater than 999 will have been replaced with 999.

Which, fortuitously enough, is just what we wanted them to be replaced with.

That should do the trick, LK. If for some reason it doesn’t do the trick, just let us know and we’ll write you a new script. In fact, we might write you a new script anyway. In fact, we might write everyone a new script, as long as that means we can stay away from the Scripting Jungle.

ScriptingGuy1

Follow