Hello Dave...thanks for responding.


Good news and bad news...........

The set foundcell did not work; I still get a type mismatch error.

The Match did work, BUT I don't think I can use it. I need to go
backwards in order to find the oldest date (prev. col, same row)
associated with that particular search value.

This is driving me Nuts!!! I've been trying different things since
yesterday. Is it a mismatch on the variables or Have I got something
else wrong in my Find?

Any and all help appreciated!

Kathy


Dave Peterson wrote:
I'd declare a range variable and set that to the result of the find:

dim FoundCell as range
set foundcell = range("v:v").cells.find(....)
if foundcell is nothing then
'no match
else
lr3 = foundcell.row
end if

========
You may want to try application.match, too:

dim res as variant 'it could be an error
res = application.match(lowcagr,range("v:v"),0)
if iserror(res) then
'no match
else
lr3 = res
end if

======
I used the whole V column.


KathyC wrote:

Hi....

I'm attempting to do a backwards search, but I keep getting a mismatch
error on the Find function and don't know why.

Range("AH5").Value = Range("H3").Value
lowcagr = Range("AH5").Value
LR3 = Range("V616").Find(What:=lowcagr, after:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, MatchCase _
:=False, SearchFormat:=False).Row

The formula in H3 is =MIN(V$15:INDIRECT(AE$25)) and it's value shows up
as:
-2.62130736985065E-02. The cell is formatted as a percentage with
2 decimals.

The variable lowcagr is defined as double and it's value shows up as
-2.62130736985065E-02
LR3 is defined as double.

The values in column V were calculated by :
ActiveCell.Value = ((tempprice / Fromprice) ^ (1 / nyears)) - 1
where tempprice, Fromprice, and nyears are all defined as double.

The column V is formatted as number with 4 decimals.

Ultimately, I'm trying to find which row in column V matches the value
in H3. The number of rows in column V changes each time the
spreadsheet is run, thereby necessitating the indirect in H3.

THanks for any help.
Kathy

PS Sorry about the formatting...I can't find any code formats to insert.

--

Dave Peterson