Hey, Scripting Guy! How Can I Search For Values in an Excel Worksheet?

ScriptingGuy1

Hey, Scripting Guy! How can I use Excel’s Find method to search for values within a worksheet?

— SK

Hey, SK. Before we begin today, we have a question for you, and for everyone else out there in Scripting Land. Back when the Scripting Guys first started planning the 2008 Winter Scripting Games (February 15^th through March 3^rd, right here in the Script Center) we gave serious thought to adding new divisions for both IronPython and Perl. In the end, however, the Official Organizing Committee for the 2008 Winter Scripting Games unanimously voted to add only one new division: Perl.

At any rate, we’ve now had an official request to add IronPython to the 2008 Scripting Games. We’re considering it, but that depends, in part, on how many people are likely to enter the IronPython division. So what do you think, guys: would you be interested in seeing IronPython added to the 2008 Scripting Games? If so, would you be interested in competing (in the Beginners and/or the Advanced Division), or would you simply be interested in being a spectator, and having the opportunity to see what IronPython is all about? Regardless, if you have an opinion on this, drop us a line at scripter@microsoft.com (in English, if possible). We’ll weigh the responses we get – both pro and con – and then make a decision in the next week or so.

Speaking of the Scripting Games (February 15^th through March 3^rd, right here in the Script Center) we have some great prizes to give away this year. Take a peek at this page, and stay tuned: we hope to have a few more things to add to the list in the next week or so.

In the meantime, we have work to do: we have to figure out how to use Microsoft Excel’s Find method to search for values within a worksheet. Let’s start by taking a look at a script that can find a specific value in a worksheet; we’ll then take a look at a slightly different approach, but an approach that’s required if we want to find a range of values.

But first things first. The following script uses the Excel Find method to locate all the cells in a spreadsheet that have a Value equal to 4:

Const xlValues = -4163

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

Set objRange = objWorksheet.UsedRange

Set objTarget = objRange.Find(4)

If Not objTarget Is Nothing Then
    Wscript.Echo objTarget.AddressLocal(False,False)
    strFirstAddress = objTarget.AddressLocal(False,False)
End If

Do Until (objTarget Is Nothing)
    Set objTarget = objRange.FindNext(objTarget)

    strHolder = objTarget.AddressLocal(False,False)
    If strHolder = strFirstAddress Then
        Exit Do
    End If

    Wscript.Echo objTarget.AddressLocal(False,False)
Loop

As you can see, we start out by defining an constant named xlValues, setting the value of this constant to -4163. (Which, by amazing coincidence, is Scripting Guy Dean Tsaltas’ favorite number.) We create an instance of the Excel.Application object, set the Visible property to True, then use the following two lines of code to open the file C:\Scripts\Test.xls and bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

As it turns out, the Find method actually belongs to Excel’s Range object; that means we have to define a range before we can call the Find method. Because we want to search the entire worksheet, we create a range that’s equal to the worksheet’s UsedRange property:

Set objRange = objWorksheet.UsedRange

In effect, that selects the range of cells that actually contain data. For example, if you have data in cell A1 and data in cell G11, the UsedRange consists of cell A1, cell G11, and all the cells in between.

After we define a range we can then search for the first instance of our target value (4) by executing this line of code:

Set objTarget = objRange.Find(4)

In its simplest form (and we won’t talk about the more complicated forms, at least not today), the Find method requires only a single parameter: the value being searched for. Specify the target value, and the Find method will hunt down the first cell that meets that criteria.

So how do we know which cell is the first cell that met our search criteria? The easiest way to do that is to check and see if the object reference objTarget is equal to Nothing:

If Not objTarget Is Nothing Then

If it is (that is, if objTarget is not a valid object reference) that means our search came up empty; in that case, the script simply ends. On the other hand, if objTarget is a valid object reference then we execute these two lines of code:

Wscript.Echo objTarget.AddressLocal(False,False)
strFirstAddress = objTarget.AddressLocal(False,False)

In the first line we’re simply echoing back the value of the AddressLocal property. Why AddressLocal as opposed to the Address property? Well, the Address property returns cell addresses that look like this:

$A$2
$B$2
$C$2

By contrast, AddressLocal (provided you set the row and column parameters to False, like we did in our example) returns cell addresses that look like this:

A2
B2
C2

We like the latter style a little better.

After echoing back the value of the AddressLocal property, we then assign that same value to a variable named strFirstAddress:

strFirstAddress = objTarget.AddressLocal(False,False)

Why do we do that? We actually have a good reason for that: the Find method is a little quirky. (And, trust us, the Scripting Guy who writes this column knows quirky when he sees it.) For one thing, the Find method finds only one instance of the target value at a time; as you’re about to see, to find additional instances of the target value you need to call the FindNext method … repeatedly.

Oh, and that’s not all. On top of that, the Find and FindNext methods don’t know when to quit. Suppose we put the FindNext method in a loop and ask it to find all the remaining instances of the target value. (Which is exactly what we’re going to do.) Then suppose FindNext locates all the values. Will it stop at that point? Nope. Instead, it loops back to the beginning of the range and begins finding the same values over and over again.

That means that we need a way to tell FindNext, “Hey, it’s time to stop; you already found this particular value.” One way to do that is to store the address of the first target value in the variable strFirstAddress. Each time FindNext finds an instance of the target value we’ll check that address against the address stored in strFirstAdrress. If they match, that means that we’ve searched the entire worksheet and it’s time to quit.

Speaking of putting the FindNext method in a loop, that’s what this block of code is for:

Do Until (objTarget Is Nothing)
    Set objTarget = objRange.FindNext(objTarget)

    strHolder = objTarget.AddressLocal(False,False)
    If strHolder = strFirstAddress Then
        Exit Do
    End If

    Wscript.Echo objTarget.AddressLocal(False,False)
Loop

What we’ve done here is configure a Do loop that runs until objTarget is no longer a valid reference; when that happens it means that we were unable to find another instance of the target value. Inside the loop we call the following line of code to find the next target value:

Set objTarget = objRange.FindNext(objTarget)

Come to think of it, this is also a bit quirky, isn’t it? Although the code looks kind of weird, all we’re doing here is calling FindNext and passing it a single parameter: objTarget, the object reference to the cell we found using the Find method. This parameter tells FindNext where to start searching; having found the first cell containing the target value we now want to pick up the search from that point. Admittedly, it looks like we’re asking FindNext to find the object reference objTarget. But looks can be deceiving: we’re just telling FindNext where to start from.

So then what happens after we call FindNext? Well, FindNext will find the next cell that meets our search criteria. When that happens, we grab the value of the AddressLocal property and store it in a variable named strHolder:

strHolder = objTarget.AddressLocal(False,False)

We then check to see if strHolder and strFirstAddress are equivalent:

If strHolder = strFirstAddress Then

If they are, that means we’ve found everything there is to find. In turn, we call the Exit Do statement and exit both the loop and the script. If the two variables aren’t equivalent then we echo back the value of the AddressLocal property, then head back to the top of the loop and try again.

Now, like we said, that works fine (and works pretty darn fast, even on large spreadsheets), provided you’re searching for a single value: a number, a string, a date, whatever. For better or worse, however, the Find method can’t work with a range of values: if you need to find all the cells with a value less than 5, well, Find isn’t going to do it. Instead, you have to use a script like this one, a script that individually looks at each and every cell in the range:

Const xlValues = -4163

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

Set objRange = objWorksheet.UsedRange

For Each objCell in objRange.Cells
    If objCell.Value < 5 Then
        Wscript.Echo objCell.AddressLocal(False,False)
    End If
Next

As you can see, after creating our Range object we set up a For Each loop to loop through each cell in that range:

For Each objCell in objRange.Cells

For each of these cells we check to see if the Value is less than 5; if it is, then we echo back the cell’s AddressLocal property. From there we loop around and check the next cell in the collection. It’s a little slower than using the Find method, but it’s the only way that we know of to look for a range of values.

And there you have it, SK; we hope that helps. By the way, we have another question for everyone: how much interest would there be if we charged everyone $20 to enter the Scripting Games, with all the money going to the Scripting Guys Vacation and Boondoggle Fund? If you like that idea just send an email to – well, never mind. You-know-who probably won’t let us do that either. In addition to logistics and reality, she has the annoying habit of worrying about whether or not things are legal.

Killjoy.

ScriptingGuy1

Follow