<\/div>\n
After echoing back the value of the AddressLocal property, we then assign that same value to a variable named strFirstAddress:<\/p>\n
strFirstAddress = objTarget.AddressLocal(False,False)\n<\/pre>\nWhy 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\u2019re about to see, to find additional instances of the target value you need to call the FindNext<\/b> method \u2026 repeatedly.<\/p>\n
Oh, and that\u2019s not all. On top of that, the Find and FindNext methods don\u2019t 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\u2019re 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. <\/p>\n
That means that we need a way to tell FindNext, \u201cHey, it\u2019s time to stop; you already found<\/i> this particular value.\u201d 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\u2019ll check that address against the address stored in strFirstAdrress. If they match, that means that we\u2019ve searched the entire worksheet and it\u2019s time to quit.<\/p>\n
Speaking of putting the FindNext method in a loop, that\u2019s what this block of code is for:<\/p>\n
Do Until (objTarget Is Nothing)\n Set objTarget = objRange.FindNext(objTarget)\n\n strHolder = objTarget.AddressLocal(False,False)\n If strHolder = strFirstAddress Then\n Exit Do\n End If\n\n Wscript.Echo objTarget.AddressLocal(False,False)\nLoop\n<\/pre>\nWhat we\u2019ve 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:<\/p>\n
Set objTarget = objRange.FindNext(objTarget)\n<\/pre>\nCome to think of it, this is also a bit quirky, isn\u2019t it? Although the code looks kind of weird, all we\u2019re 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\u2019re asking FindNext to find the object reference objTarget. But looks can be deceiving: we\u2019re just telling FindNext where to start from.<\/p>\n
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:<\/p>\n
strHolder = objTarget.AddressLocal(False,False)\n<\/pre>\nWe then check to see if strHolder and strFirstAddress are equivalent:<\/p>\n
If strHolder = strFirstAddress Then\n<\/pre>\nIf they are, that means we\u2019ve found everything there is to find. In turn, we call the Exit Do<\/b> statement and exit both the loop and the script. If the two variables aren\u2019t<\/i> equivalent then we echo back the value of the AddressLocal property, then head back to the top of the loop and try again.<\/p>\n
Now, like we said, that works fine (and works pretty darn fast, even on large spreadsheets), provided<\/i> you\u2019re searching for a single value: a number, a string, a date, whatever. For better or worse, however, the Find method can\u2019t work with a range of values: if you need to find all the cells with a value less than 5, well, Find isn\u2019t 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:<\/p>\nConst xlValues = -4163\n\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\n\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(\"Sheet1\")\n\nSet objRange = objWorksheet.UsedRange\n\nFor Each objCell in objRange.Cells\n If objCell.Value < 5 Then\n Wscript.Echo objCell.AddressLocal(False,False)\n End If\nNext\n<\/pre>\nAs you can see, after creating our Range object we set up a For Each loop to loop through each cell in that range:<\/p>\n
For Each objCell in objRange.Cells\n<\/pre>\nFor each of these cells we check to see if the Value is less than 5; if it is, then we echo back the cell\u2019s AddressLocal property. From there we loop around and check the next cell in the collection. It\u2019s a little slower than using the Find method, but it\u2019s the only way that we know of to look for a range of values.<\/p>\n
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<\/i> idea just send an email to \u2013 well, never mind. You-know-who probably won\u2019t let us do that<\/i> either. In addition to logistics and reality, she has the annoying habit of worrying about whether or not things are legal.<\/p>\n
Killjoy.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I use Excel\u2019s 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 15th […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-56393","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I use Excel\u2019s 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 15th […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56393","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=56393"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56393\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=56393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56393"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a> <\/p>\n