FIND type mismatch error, but don't see it

[KathyC]

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]

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]

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

 

[KathyC]

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

 

[KathyC]

OK, It bombed because the Find didn't actually Find anything and I
didn't put the test for Nothing in because I know that what i'm looking
for is there, having just found it using the MIN function in the
worksheet cell. Long sentence, sorry.


Still at a loss.................

 

[Dave Peterson]

What worried me was if there was any rounding errors (I didn't set up a test
worksheet).

Maybe it's time to just loop from the bottom up looking for a match--or a very
close approximation.

if abs(somevalue-othervalue) <.0000001 then
'close enough
....

 

[KathyC]

Dave Thanks for trying.


I think I'm going to take the easy route and just have the user input a
row number instead of trying to have it be automatic.

Won't be as nice, but it won't be as much trouble either!

 

[Guest]

If you manually find the match, try =(x-y) to see the difference that FIND is
seeing. Note that the parentheses are required, otherwise Excel will gloss
over the differences that are causing this issue.

Excel (and almost all general purpose software) does binary math per the
IEEE 754 standard. There are 29 distinct binary values that all display as
-2.62130736985065E-02
You cannot directly see these differences, as MS has arbitrarily chosen to
display no more than 15 figures, even though 17 are required to uniquely
identify a binary representation. You can indirectly see them by subtraction
(as I initially suggested) or by using software that displays extended
precision, such as
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

In Excel, to match 15-figure values, you could use the array formula
=MATCH(ROUND(x,16),ROUND(data,16),0)
In VBA you can use Excel array formulas via the Evaluate function. If a 15
figure match might require rounding to a different number of decimal places,
see
http://groups.google.com/group/microsoft.public.excel.programming/msg/2b244c8f41e91025

Jerry