+ Reply to Thread
Results 1 to 6 of 6

Range.Find function fails to find a match but For loop confirms that match exists

  1. #1
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,117

    Range.Find function fails to find a match but For loop confirms that match exists

    The attached file has a list of numeric constants in A1:A10. The VBA code in module Failure uses the Range.Find method to find the maximum value in this range but the result is Nothing. However, I wrote a small function that loops through the range and it correctly finds the desired value.

    I have never seen this behavior for Find. Am I on double-secret probation and it won't work for me?

    I have attached the file but for a quick look here is the code.

    Please Login or Register  to view this content.
    The output of this code is:

    Using Find: not found!
    Using FindValue: Found in $A$4
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,508
    Have you tried using some of the other arguments Find used, eg After?
    If posting code please use code tags, see here.

  3. #3
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Range.Find function fails to find a match but For loop confirms that match exists

    try with LookIn:=xlFormulas
    If solved remember to mark Thread as solved

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,544

    Re: Range.Find function fails to find a match but For loop confirms that match exists

    Change this line of code

    Please Login or Register  to view this content.
    To

    Please Login or Register  to view this content.
    and see if this helps.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,117

    Re: Range.Find function fails to find a match but For loop confirms that match exists

    Quite surprisingly, changing xlValues to xlFormulas causes it to work. I cannot understand why; this is a complaint I have about Microsoft's documentation for functions like this. It is not sufficiently comprehensive and precise.

    However, my attachment is a boiled-down version of another file where the search range is formulas; in my version I did a Paste Values to get constants. When I modify the file to make these formulas, it no longer works, no matter whether I used xlValues or xlFormulas. See attached.

    Norie, I have reviewed all available arguments and have tried all the ones that makes sense for this situation (I started out with only What). I have not used After because the documentation says

    Quote Originally Posted by Microsoft
    If you do no [sic] specify this argument, the search starts after the cell in the upper-left corner of the range.
    When I explicitly provide After it still doesn't work. Other arguments are:

    SearchOrder: Search order doesn't matter for this search. There is only one column being searched.
    SearchDirection: Search direction doesn't matter for this search. There is only one matching value; normally you use this when you want to find the first match in a particular direction.
    MatchCase: Doesn't apply to numeric data
    MatchByte: Doesn't apply to numeric data
    SearchFormat: No search format is specified, this doesn't apply. (Very poorly documented, see above link)
    Attached Files Attached Files

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,117

    Re: Range.Find function fails to find a match but For loop confirms that match exists

    sktneer, that changed worked--sort of. It worked, then I changed the format of the cells to show more decimal places and it stopped working. Does Find look at what is displayed rather than the underlying stored value?

    What I really need is a very rigorous specification for Find and Microsoft just doesn't seem to be have it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Problem with Find function. Need to find Exact match
    By SMILE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2012, 07:07 AM
  2. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  3. Replies: 3
    Last Post: 10-27-2010, 08:05 PM
  4. method 'range' of object '_global' failed - find function sometimes fails?
    By n1210933 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-06-2010, 06:17 AM
  5. Loop a column on Sheet1 and loop a row on Sheet2 to find a match
    By johnnywinter in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2009, 02:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1