FIND type mismatch error, but don't see it
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!
Dave Peterson wrote:
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 wrote:
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.................
KathyC wrote:
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
--
Dave Peterson
|