[Dick Kusleika]

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

[Brad[_7_]]

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


.

[Dick Kusleika]

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


.

[Brad[_7_]]

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


.



.