Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default FIND type mismatch error, but don't see it

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default FIND type mismatch error, but don't see it

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default FIND type mismatch error, but don't see it

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default FIND type mismatch error, but don't see it

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default FIND type mismatch error, but don't see it

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default FIND type mismatch error, but don't see it

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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default FIND type mismatch error, but don't see it

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
run-time 13 Type mismatch simonsmith Excel Discussion (Misc queries) 2 May 18th 06 04:14 PM
Select rows and sort based on type Sarah Excel Discussion (Misc queries) 0 October 11th 05 05:06 PM
Trapping a NO FIND after a find Alan Excel Discussion (Misc queries) 2 August 31st 05 01:28 PM
Find last occurance of text in range farutherford Excel Worksheet Functions 5 August 30th 05 02:00 AM
Why type mismatch - R/T error 13 Jim May Excel Discussion (Misc queries) 5 January 9th 05 07:45 PM


All times are GMT +1. The time now is 12:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"