Excel - Problems with the Find method

[Goppi]

I actually wrote earlier today another thread and I thought the Subject of this Thread describes the problem better:

In my module the Find method fails on 2 different values (I've used Find a few times more, but with success - ;-) ):
1.) 0 (zero) : The zero is being returned by VLookup whereas the cell that should have been returned was empty. The format of the cell that I try to find is percentage.
The method Find on "0" fails - but on "0.00%" it works. After this result I came to the conclusion that the method Find does maybe not lookup the value of the cell itself, but more or less the displayed value.

2.) "01/06/2003" (date) : Based on my previous conclusion I tried both versions "01/06/2003" and "37773" - none of them were successfull.

If anybody could explain me how Find works in detail - I would appreciate it.

cheers,
Goppi

Hoc nomen meum verum non est.

 

[Goppi]

It seems so I'm faster than I should be ;-)

I found again a work-around for the second case: It works when I convert the string to a date with CDate

I just don't understand why: Excel shows in the Formula Bar (below the command bars) the date as a string and in regards to case number one the 0.00% as a 0 - which is correct, the values are either imported as a string or the result of the VLookup function. But internally it seems so, that the values are stored in a different way.

cheers,
Goppi

Id imperfectum manet dum confectum erit.

 

[xlbo]

Find works differently depending on whether you are using
Lookin:=xlFormulas
or
Lookin:=xlValues

Also beahves differently for lookat:=xlWhole or xlPart

For each combination, there is a minimum spec for finding a match eg for xlValues and xlWhole, you would need the right value and the right format as well

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244