Oh, with the Foundcell.......

I changed lowcagr and LR3 to be defined as Variant. That got me
through the Find statement! But then I bombed on LR3=foundcell.row
with:

Object Variable or WithBlock Variable Not Set


?????

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