{"id":64153,"date":"2007-08-25T01:42:00","date_gmt":"2007-08-25T01:42:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/08\/25\/how-can-i-replace-numeric-values-in-an-excel-spreadsheet\/"},"modified":"2007-08-25T01:42:00","modified_gmt":"2007-08-25T01:42:00","slug":"how-can-i-replace-numeric-values-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-replace-numeric-values-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Replace Numeric Values in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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\u2019s supposed<\/I> 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\u2019t 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 <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019t actually take a vow<\/I> of poverty; instead, he sort of had poverty forced upon him. That\u2019s 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\u2019s a little concerned about anyone who comes to him requesting help with \u201cfix-up work.\u201d Fix-up work?!? You obviously haven\u2019t seen the Scripting House lately, have you?<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. OK, so, in all fairness, no one<\/I> has seen the Scripting House lately; with the Scripting Family having been out of town for a couple weeks, the Scripting House is now hidden behind a jungle of uncut grass and overgrown weeds.<\/P>\n

Or at least we assume<\/I> the Scripting House is still back there behind the jungle of uncut grass and overgrown weeds. It\u2019s kind of hard to tell.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

At any rate, fix-up work is definitely not what the Scripting Guy who writes this column does best. But maybe that isn\u2019t his fault. Instead, maybe that\u2019s because there isn\u2019t 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:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
Set objRange = objWorksheet.UsedRange<\/p>\n
For Each objCell in objRange\n    If IsNumeric(objCell.Value) Then\n        If objCell.Value > 999 Then\n            objCell.Value = 999\n        End If\n    End If\nNext\n<\/PRE>\n
As you can see, there really isn\u2019t much to<\/I> this script; as it turns out, it\u2019s 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<\/B> object and then set the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
Needless to say, there\u2019s nothing too terribly hard about any of that.<\/P>\n
Of course, we know what you\u2019re thinking: \u201cSure, there\u2019s nothing terribly hard about any of that<\/I>. But now we have to figure out how to do a search-and-replace operation that can find \u2013 and then replace \u2013 any values greater than 999. How hard is that<\/I> going to be?\u201d<\/P>\n
Well, to tell you the truth, that might be very<\/I> hard; we don\u2019t know for sure. That\u2019s because we aren\u2019t going to do a true search-and-replace operation (that is, we aren\u2019t going to use Excel\u2019s Replace<\/B> 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\u2019re 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\u2019re about to see, there\u2019s nothing too terribly hard about any of that, either.<\/P>\n
What\u2019s that? You don\u2019t believe us? You still think that this search and replace thing is going to be hard? Let\u2019s see if we can put your mind at ease by explaining exactly how this all works. <\/P>\n
To begin with, we use this line of code and the UsedRange<\/B> property to grab a collection of all the cells in the worksheet. Nothing too terribly hard there:<\/P>
Set objRange = objWorksheet.UsedRange\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. OK, we need to clarify this a little. The UsedRange property doesn\u2019t necessarily return all<\/I> the cells in the spreadsheet; instead, it returns the range of cells that actually contain data. For example, suppose you have data in cell A1 and another piece of data in cell D5. The UsedRange property returns cells A1 through<\/I> D5; that is, the first cell that has data in it, the last cell that has data in it, and any cells in between.<\/P>\n
It goes without saying that the UsedRange property is a very handy little property to know about.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
When you create an instance of Excel\u2019s Range<\/B> 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 \u2013 surprise, surprise \u2013 that\u2019s exactly what we do next:<\/P>
For Each objCell in objRange\n<\/PRE>\n
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\u2019s not, we\u2019re 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\u2019s IsNumeric<\/B> function to determine whether or not the value in the first cell happens to be a number:<\/P>
If IsNumeric(objCell.Value) Then\n<\/PRE>\n
If IsNumeric returns False that means we\u2019re 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\u2019s assume, however, that IsNumeric returns True; that means that we are<\/I> dealing with a numeric value. In that case, we then use this line of code to determine if that value is greater than 999:<\/P>
If objCell.Value > 999 Then\n<\/PRE>\n
And what if the value is<\/I> greater than 999? No problem; we simply assign a new value (999) to the cell:<\/P>
objCell.Value = 999\n<\/PRE>\n
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\u2019ve looped through the entire collection of cells any values greater than 999 will have been replaced with 999.<\/P>\n
Which, fortuitously enough, is just what we wanted them to be replaced with.<\/P>\n
That should do the trick, LK. If for some reason it doesn\u2019t<\/I> do the trick, just let us know and we\u2019ll write you a new script. In fact, we might write you a new script anyway. In fact, we might write everyone<\/I> a new script, as long as that means we can stay away from the Scripting Jungle.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. How bad could the Scripting Jungle really be? Let\u2019s put it this way: eyewitnesses claim that there are soldiers living in that jungle who still don\u2019t know that World War II is over<\/A>. To be honest, that\u2019s a little hard to believe.<\/P>\n
Although, come to think of it, we haven\u2019t seen the Scripting Son since we returned home from Italy. Hmmmm, you don\u2019t suppose \u2026.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s supposed to be entered as 999, the maximum-allowed value. However, people have a tendency to […]<\/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-64153","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! 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\u2019s supposed to be entered as 999, the maximum-allowed value. However, people have a tendency to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64153","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=64153"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64153\/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=64153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}