Hey guys

The below code finds the address of the first occurance of
the specified value (variable). Lets say the variable is
equal to "Mouse". Lets say mouse was located in cell C4,
C100, and C322. When the code is run, test would equal
C322 because cell C322 is the location of the first
instance of that value it sees. This works fine accept
when the variable is equal to a date.

In this case, variable CL.Value = 06/22/2004. When the
below code is run, the result is empty. What do I need to
do to fix this?

test = Range("C65536").End(xlUp).Find(CL.Value).Address

Maybe change to (untested):

test = Range("C65536").End(xlUp).Find(CDate(CL.Value)).Ad dress

to get it to a date. In any case the problem is surely a date vs. tex
comparison issue (or vice-versa).



--
Message posted from http://www.ExcelForum.com

If you've shown your exact code in the message, the value you are looking for
is 6 divided by 22 divided by 2004. It's not the date June 22, 2004.

You must specify dates as either the date serial number or a literal date. The
format for the latter is to surround the date with pound signs, e.g.
#8/11/2004#.

Try it with CL.Value = #6/22/2004#

or

CL.Value = DateSerial(2004,6,22)


On Wed, 11 Aug 2004 11:53:49 -0700, "Todd Huttenstine"
wrote:

Hey guys

The below code finds the address of the first occurance of
the specified value (variable). Lets say the variable is
equal to "Mouse". Lets say mouse was located in cell C4,
C100, and C322. When the code is run, test would equal
C322 because cell C322 is the location of the first
instance of that value it sees. This works fine accept
when the variable is equal to a date.

In this case, variable CL.Value = 06/22/2004. When the
below code is run, the result is empty. What do I need to
do to fix this?

test = Range("C65536").End(xlUp).Find(CL.Value).Address

test = Range("C65536").End(xlUp).Find(CL.Value).Address

Are you aware that your code above only searches a single cell ?
I strongly doubt that's really what you're wanting to do.


The code below will search from C1, to the last populated cell in Column C.
It will return the address of the first occurence of CL.Value.
test = Range(Range("C1"), Range("C65536").End(xlUp)).Find(CL.Value).Address


The code below will search for the last occurence of CL.Value.
test = Range(Range("C1"), Range("C65536").End(xlUp)).Find( _
What:=CL.Value, _
After:=Range("C1"), _
SearchDirection:=xlPrevious).Address


Regards,
Vic Eldridge



"Todd Huttenstine" wrote in message ...
Hey guys

The below code finds the address of the first occurance of
the specified value (variable). Lets say the variable is
equal to "Mouse". Lets say mouse was located in cell C4,
C100, and C322. When the code is run, test would equal
C322 because cell C322 is the location of the first
instance of that value it sees. This works fine accept
when the variable is equal to a date.

In this case, variable CL.Value = 06/22/2004. When the
below code is run, the result is empty. What do I need to
do to fix this?

test = Range("C65536").End(xlUp).Find(CL.Value).Address