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
Bookmarks