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/micro...06871cf92f8465
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/micro...244c8f41e91025
Jerry
"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.