Results 1 to 6 of 6

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

Threaded View

6StringJazzer Range.Find function fails to... 12-08-2013, 12:15 PM
Norie Have you tried using some of... 12-08-2013, 01:15 PM
patel45 Re: Range.Find function fails... 12-08-2013, 01:22 PM
sktneer Re: Range.Find function fails... 12-08-2013, 01:37 PM
6StringJazzer Re: Range.Find function fails... 12-08-2013, 01:37 PM
6StringJazzer Re: Range.Find function fails... 12-08-2013, 03:35 PM
  1. #1
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner VA USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,431

    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.

    Sub FindMax()
      
       Dim Found As Range
       Dim SearchRange As Range
       
       With Worksheets("Data")
       
          Set SearchRange = .Range("A1:A10")
           
          Set Found = SearchRange.Find(what:=Application.WorksheetFunction.Max(SearchRange), LookIn:=xlValues, lookat:=xlWhole)
          If Found Is Nothing Then
             Debug.Print "Using Find: not found!"
          Else
             Debug.Print "Using Find: Found at " & Found.Address
          End If
          
          Set Found = FindValue(SearchRange, Application.WorksheetFunction.Max(SearchRange))
          If Found Is Nothing Then
             Debug.Print "Using FindValue: not found!"
          Else
             Debug.Print "Using FindValue: Found in " & Found.Address
          End If
          
          If Found Is Nothing Then
             MsgBox "Could not find maximum value " & Application.WorksheetFunction.Max(SearchRange) & " in " & SearchRange.Address
          Else
             ' do some TBD action
          End If
          
       End With
       
       
    End Sub
    
    Function FindValue(R As Range, V As Variant) As Range
    
       Dim C As Range
       For Each C In R
          If C.Value = V Then
             Set FindValue = C
             Exit Function
          End If
       Next C
       Set FindValue = Nothing
    
    End Function
    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]

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