The Find Method
Brad
The After argument takes a range argument, so Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=Worksheets("Inquries").Range(strMaxRange), _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) should work. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Brad" wrote in message ... Good afternoon, I'm completely stumped here. What I need is to find a certain value, by searching from the bottom of the spreadsheet in a single column. Then take that cell location of where the value is found, and cut (from cut and paste) columns A - G and paste them in another worksheet that I created. Set rngRacf = Worksheets("Inquiries").Range("C2:" & strMaxRange) Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=strMaxRange, _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) strMaxRange is the second to last cell value that has data. Assuming that the "After" designation will begin the search in the very last cell, searching backwards for an instance of the value "0000" that I'm searching for. I want rngBottomZero to have the cell location where the criteria is found. If I just use a variant "BottomZero" the data that I get back is just the "0000" value it searched for. In this particular code I receive "Unable to get the Find property of the range class." Which is even more confusing to me because I thought Find was a method, not a property. But I'm not sure how to designate it otherwise. I'm numb from troubleshooting and I'm getting nowhere. Any help would be greatly appreciated. -Brad |
The Find Method
Thank you for the help Dick. However, after I modified my
code to include your addition, I received: "Subscript out of range". Debugging it, I find that "strMaxRange" is "C4116" out of 4117 rows of data in the range specified. I even changed the strMaxRange variable to a literal "C100" to see what would happen, and I got the same error: "Subscript out of Range". I can't make any sense of the help file's description of the error message, which was: <<<<<< Subscript out of range (Error 9) Elements of arrays and members of collections can only be accessed within their defined ranges. This error has the following causes and solutions: You referenced a nonexistent array element. The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you're working with arrays that are redimensioned. If the index is specified as a variable, check the spelling of the variable name. You declared an array but didn't specify the number of elements. For example, the following code causes this error: Dim MyArray() As Integer MyArray(8) = 234 ' Causes Error 9. Visual Basic doesn't implicitly dimension unspecified array ranges as 0 - 10. Instead, you must use Dim or ReDim to specify explicitly the number of elements in an array. You referenced a nonexistent collection member. Try using the For Each...Next construct instead of specifying index elements. You used a shorthand form of subscript that implicitly specified an invalid element. For example, when you use the ! operator with a collection, the ! implicitly specifies a key. For example, object!keyname.value is equivalent to object.item (keyname).value. In this case, an error is generated if keyname represents an invalid key in the collection. To fix the error, use a valid key name or index for the collection. The actual range I'm trying the find in is exactly one cell larger than the subscript I'm using, and herein lies the source of my confusion. Also, after re-reading the description of the parameter "After" for the 50th time I see that it says "If you don't specify this argument, the search starts after the cell in the upper-left corner of the range." And since I'm saying "xlPrevious" in the direction, it should automatically start at the bottom and this entire logic is a waste of time and resources. And when I do that, it doesn't give me an error, and it just returns "0000" to rngBottomZero instead of the actual Cell address. Even though the return of the method, according to the help file, is "Finds specific information in a range, and returns a Range object that represents the first cell where that information is found". Finally, when I add ".Address" to the end of it, in an attempt to get a cell location instead of a value, it gives me an "Object Required" error. Any recommendations for corrections or other method implementation are very welcome at this time. Thanks for reading my spam and your patience with a VBA newbie. -Brad -----Original Message----- Brad The After argument takes a range argument, so Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=Worksheets("Inquries").Range (strMaxRange), _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) should work. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Brad" wrote in message ... Good afternoon, I'm completely stumped here. What I need is to find a certain value, by searching from the bottom of the spreadsheet in a single column. Then take that cell location of where the value is found, and cut (from cut and paste) columns A - G and paste them in another worksheet that I created. Set rngRacf = Worksheets("Inquiries").Range("C2:" & strMaxRange) Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=strMaxRange, _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) strMaxRange is the second to last cell value that has data. Assuming that the "After" designation will begin the search in the very last cell, searching backwards for an instance of the value "0000" that I'm searching for. I want rngBottomZero to have the cell location where the criteria is found. If I just use a variant "BottomZero" the data that I get back is just the "0000" value it searched for. In this particular code I receive "Unable to get the Find property of the range class." Which is even more confusing to me because I thought Find was a method, not a property. But I'm not sure how to designate it otherwise. I'm numb from troubleshooting and I'm getting nowhere. Any help would be greatly appreciated. -Brad . |
The Find Method
Brad
Look at Dave Petersons response. You're correct (and he's one step ahead of us) that you don't need to specify after because the xlPrevious is doing what you want anyway. Dave does specify an After argument, but as you said the top left is the default. There may be an advantage to specifying it, but I'm not sure. If you want to understand the Subscript error, post the code that gives you the error. Subscript errors are usually pretty easy to troubleshoot. Screwing up a range reference (syntactically) generally gives you an "Application defined error", so the likely culprit is in your Worksheets(...), such as misspelling "Inquiries." I'm not sure what's causing the Object Required error, but here's a little info on Ranges: You are setting a variable to a range with the Set statement. Presumably you have Dimmed the variable as Range. That variable is an object variable. Object variables are set using the Set statement while scalar variables (String, Long, Boolean) are set without the Set statement. A statement like this Set rngFound = rngLook.Find(What:...) sets the object variable rngFound to a particular range (or to Nothing). This variable now points to the range, it doesn't contain the cell address or the value or anything like that, it just points to that range. Once set, you can now retrieve any property of that range (like cell address or value). Maybe a couple of examples would help Dim rngFound as Range Set rngFound.Address = rngLook.Find(What:...) This won't work for a variety of reasons. First the Address property is read only - you don't get to set a range's address only retrieve it. Even if it wasn't read only, you wouldn't use the Set statement to assign a non-object variable. If you wanted to retrieve the address, you could do it like this Dim strFound as String strFound = rng.Look.Find(What:...).Address The Address property returns a string so we assign it to a string variable and don't use the Set statement. The Find method returns a range object, so we can use the Address property directly on the Find method. The problem with this method is that if nothing is found, you will get an error. In Dave's example, he "Sets" the Find method to a Range, then tests the Range against "Is Nothing" before accessing any properties of the Range. Nothing is a keyword in VBA that means that the object variable doesn't point to an object. If the result of the Find method returns Nothing, then you can have a nice message that says nothing was found or do something else in the code. If the Find method returns Not Nothing (points to an actual range) then you can access the properties and methods of the range object depending on what you intend to do in the code. Dave accesses the Row property of the range. Sorry for the long post. Post back if you need further clarification. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Brad" wrote in message ... Thank you for the help Dick. However, after I modified my code to include your addition, I received: "Subscript out of range". Debugging it, I find that "strMaxRange" is "C4116" out of 4117 rows of data in the range specified. I even changed the strMaxRange variable to a literal "C100" to see what would happen, and I got the same error: "Subscript out of Range". I can't make any sense of the help file's description of the error message, which was: <<<<<< Subscript out of range (Error 9) Elements of arrays and members of collections can only be accessed within their defined ranges. This error has the following causes and solutions: You referenced a nonexistent array element. The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you're working with arrays that are redimensioned. If the index is specified as a variable, check the spelling of the variable name. You declared an array but didn't specify the number of elements. For example, the following code causes this error: Dim MyArray() As Integer MyArray(8) = 234 ' Causes Error 9. Visual Basic doesn't implicitly dimension unspecified array ranges as 0 - 10. Instead, you must use Dim or ReDim to specify explicitly the number of elements in an array. You referenced a nonexistent collection member. Try using the For Each...Next construct instead of specifying index elements. You used a shorthand form of subscript that implicitly specified an invalid element. For example, when you use the ! operator with a collection, the ! implicitly specifies a key. For example, object!keyname.value is equivalent to object.item (keyname).value. In this case, an error is generated if keyname represents an invalid key in the collection. To fix the error, use a valid key name or index for the collection. The actual range I'm trying the find in is exactly one cell larger than the subscript I'm using, and herein lies the source of my confusion. Also, after re-reading the description of the parameter "After" for the 50th time I see that it says "If you don't specify this argument, the search starts after the cell in the upper-left corner of the range." And since I'm saying "xlPrevious" in the direction, it should automatically start at the bottom and this entire logic is a waste of time and resources. And when I do that, it doesn't give me an error, and it just returns "0000" to rngBottomZero instead of the actual Cell address. Even though the return of the method, according to the help file, is "Finds specific information in a range, and returns a Range object that represents the first cell where that information is found". Finally, when I add ".Address" to the end of it, in an attempt to get a cell location instead of a value, it gives me an "Object Required" error. Any recommendations for corrections or other method implementation are very welcome at this time. Thanks for reading my spam and your patience with a VBA newbie. -Brad -----Original Message----- Brad The After argument takes a range argument, so Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=Worksheets("Inquries").Range (strMaxRange), _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) should work. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Brad" wrote in message ... Good afternoon, I'm completely stumped here. What I need is to find a certain value, by searching from the bottom of the spreadsheet in a single column. Then take that cell location of where the value is found, and cut (from cut and paste) columns A - G and paste them in another worksheet that I created. Set rngRacf = Worksheets("Inquiries").Range("C2:" & strMaxRange) Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=strMaxRange, _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) strMaxRange is the second to last cell value that has data. Assuming that the "After" designation will begin the search in the very last cell, searching backwards for an instance of the value "0000" that I'm searching for. I want rngBottomZero to have the cell location where the criteria is found. If I just use a variant "BottomZero" the data that I get back is just the "0000" value it searched for. In this particular code I receive "Unable to get the Find property of the range class." Which is even more confusing to me because I thought Find was a method, not a property. But I'm not sure how to designate it otherwise. I'm numb from troubleshooting and I'm getting nowhere. Any help would be greatly appreciated. -Brad . |
The Find Method
I can't thank you enough for that response Dick, that
explained innumerable questions I had about Excel's OOP. I got it working now by modifying my code. Thank you. -----Original Message----- Brad Look at Dave Petersons response. You're correct (and he's one step ahead of us) that you don't need to specify after because the xlPrevious is doing what you want anyway. Dave does specify an After argument, but as you said the top left is the default. There may be an advantage to specifying it, but I'm not sure. If you want to understand the Subscript error, post the code that gives you the error. Subscript errors are usually pretty easy to troubleshoot. Screwing up a range reference (syntactically) generally gives you an "Application defined error", so the likely culprit is in your Worksheets(...), such as misspelling "Inquiries." I'm not sure what's causing the Object Required error, but here's a little info on Ranges: You are setting a variable to a range with the Set statement. Presumably you have Dimmed the variable as Range. That variable is an object variable. Object variables are set using the Set statement while scalar variables (String, Long, Boolean) are set without the Set statement. A statement like this Set rngFound = rngLook.Find(What:...) sets the object variable rngFound to a particular range (or to Nothing). This variable now points to the range, it doesn't contain the cell address or the value or anything like that, it just points to that range. Once set, you can now retrieve any property of that range (like cell address or value). Maybe a couple of examples would help Dim rngFound as Range Set rngFound.Address = rngLook.Find(What:...) This won't work for a variety of reasons. First the Address property is read only - you don't get to set a range's address only retrieve it. Even if it wasn't read only, you wouldn't use the Set statement to assign a non-object variable. If you wanted to retrieve the address, you could do it like this Dim strFound as String strFound = rng.Look.Find(What:...).Address The Address property returns a string so we assign it to a string variable and don't use the Set statement. The Find method returns a range object, so we can use the Address property directly on the Find method. The problem with this method is that if nothing is found, you will get an error. In Dave's example, he "Sets" the Find method to a Range, then tests the Range against "Is Nothing" before accessing any properties of the Range. Nothing is a keyword in VBA that means that the object variable doesn't point to an object. If the result of the Find method returns Nothing, then you can have a nice message that says nothing was found or do something else in the code. If the Find method returns Not Nothing (points to an actual range) then you can access the properties and methods of the range object depending on what you intend to do in the code. Dave accesses the Row property of the range. Sorry for the long post. Post back if you need further clarification. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Brad" wrote in message ... Thank you for the help Dick. However, after I modified my code to include your addition, I received: "Subscript out of range". Debugging it, I find that "strMaxRange" is "C4116" out of 4117 rows of data in the range specified. I even changed the strMaxRange variable to a literal "C100" to see what would happen, and I got the same error: "Subscript out of Range". I can't make any sense of the help file's description of the error message, which was: <<<<<< Subscript out of range (Error 9) Elements of arrays and members of collections can only be accessed within their defined ranges. This error has the following causes and solutions: You referenced a nonexistent array element. The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you're working with arrays that are redimensioned. If the index is specified as a variable, check the spelling of the variable name. You declared an array but didn't specify the number of elements. For example, the following code causes this error: Dim MyArray() As Integer MyArray(8) = 234 ' Causes Error 9. Visual Basic doesn't implicitly dimension unspecified array ranges as 0 - 10. Instead, you must use Dim or ReDim to specify explicitly the number of elements in an array. You referenced a nonexistent collection member. Try using the For Each...Next construct instead of specifying index elements. You used a shorthand form of subscript that implicitly specified an invalid element. For example, when you use the ! operator with a collection, the ! implicitly specifies a key. For example, object!keyname.value is equivalent to object.item (keyname).value. In this case, an error is generated if keyname represents an invalid key in the collection. To fix the error, use a valid key name or index for the collection. The actual range I'm trying the find in is exactly one cell larger than the subscript I'm using, and herein lies the source of my confusion. Also, after re-reading the description of the parameter "After" for the 50th time I see that it says "If you don't specify this argument, the search starts after the cell in the upper-left corner of the range." And since I'm saying "xlPrevious" in the direction, it should automatically start at the bottom and this entire logic is a waste of time and resources. And when I do that, it doesn't give me an error, and it just returns "0000" to rngBottomZero instead of the actual Cell address. Even though the return of the method, according to the help file, is "Finds specific information in a range, and returns a Range object that represents the first cell where that information is found". Finally, when I add ".Address" to the end of it, in an attempt to get a cell location instead of a value, it gives me an "Object Required" error. Any recommendations for corrections or other method implementation are very welcome at this time. Thanks for reading my spam and your patience with a VBA newbie. -Brad -----Original Message----- Brad The After argument takes a range argument, so Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=Worksheets("Inquries").Range (strMaxRange), _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) should work. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Brad" wrote in message ... Good afternoon, I'm completely stumped here. What I need is to find a certain value, by searching from the bottom of the spreadsheet in a single column. Then take that cell location of where the value is found, and cut (from cut and paste) columns A - G and paste them in another worksheet that I created. Set rngRacf = Worksheets("Inquiries").Range("C2:" & strMaxRange) Set rngBottomZero = rngRacf.Find( What:="0000", _ After:=strMaxRange, _ LookIn:=xlValues, _ SearchDirection:=xlPrevious) strMaxRange is the second to last cell value that has data. Assuming that the "After" designation will begin the search in the very last cell, searching backwards for an instance of the value "0000" that I'm searching for. I want rngBottomZero to have the cell location where the criteria is found. If I just use a variant "BottomZero" the data that I get back is just the "0000" value it searched for. In this particular code I receive "Unable to get the Find property of the range class." Which is even more confusing to me because I thought Find was a method, not a property. But I'm not sure how to designate it otherwise. I'm numb from troubleshooting and I'm getting nowhere. Any help would be greatly appreciated. -Brad . . |
All times are GMT +1. The time now is 01:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com