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
.