CODEWITHSUNDEEP
excelvba
Toddleson
Toddleson

Reputation: 4457

Finding Nothing with Range.Find

I have read the documentation and have been using the method with mixed success but I keep running into situations where the object I'm looking for is obviously right there but Range.Find returns nothing.

test = Search_Range.Cells(4,4).Value = Search_Text 'returns true
set test2 = Search_Range.Find(What:=Search_Text) 'returns nothing

I'm so frustrated! The target cell is within the Search_Range. The Search_Range is on the correct sheet. The data type of the value is the same as Search_Text. Even if I add things like LookAt:=xlPart or LookAt:=xlWhole, nothing changes. Same with LookIn:=xlValues.

What other sources could be causing the method to return nothing?

Here is the function causing me to rip my hair out. I made it so the user could quickly identify what the macro failed to find.

Private Function Look_For(ByVal Search_Text As String, ByRef Search_Range As Range, _
                            Optional LookAt As Long = xlPart, _
                            Optional Error_Message As Boolean = True) As Variant
    On Error Resume Next
    Set Look_For = Search_Range.Find(What:=Search_Text, LookAt:=LookAt, MatchCase:=False)
    On Error GoTo 0
    
    If Look_For Is Nothing Then
        Look_For = False
        If Error_Message = True Then MsgBox "Could not find """ & Search_Text & _
            """ in the area " & Search_Range.Address & "." & vbNewLine & _
            "Please fix the sheet or this macro and try again.", _
             vbCritical, "Fatal Error!"
    End If

End Function

Edit: Here is a sub that can recreate the issue.

Sub Test()
    Const Txt As String = "FSQP 4.16-04F Skid Detail Sheet"
    Dim target As Range
    If Not Look_For(Txt, Sheet1.Range("A1:D8")) = False Then
        Set target = Look_For(Txt, Sheet1.Range("A1:D8"))
    End If
    If target Is Nothing Then MsgBox "Minimal Reproducible Example"
End Sub

There is no error message, I am just trying to get a cell object using Range.Find and save it to a variable but I am getting nothing from Range.Find

my spreadsheet

Upvotes: 2

Views: 383

Answers (1)

Toddleson
Toddleson

Reputation: 4457

Thanks to advice from John Coleman, BigBen and Tim Williams, I was able to find the answer to this problem.

The problem is that Range.Find was not able to handle a merged cell that was partially outside the defined range. The cell we were looking for in D4 was actually merged D4:F4. To avoid this problem, I edited the function to check and expand the search range prior to doing Range.Find.

Here is the revised function

Private Function Look_For(ByVal Search_Text As String, ByRef Search_Range As Range, _
                            Optional LookAt As Long = xlPart, _
                            Optional Error_Message As Boolean = True) As Variant
                            
    Dim oCell As Range, mCell As Range
    For Each oCell In Search_Range
        If oCell.MergeCells = True Then
            For Each mCell In oCell.MergeArea
                If Intersect(mCell, Search_Range) Is Nothing Then
                    Set Search_Range = Union(Search_Range, oCell.MergeArea)
                End If
            Next mCell
        End If
    Next oCell

    Set Look_For = Search_Range.Find(what:=Search_Text, LookAt:=LookAt, MatchCase:=False)

    If Look_For Is Nothing Then
        Look_For = False
        If Error_Message = True Then MsgBox "Could not find """ & Search_Text & _
            """ in the area " & Search_Range.Address & "." & vbNewLine & _
            "Please fix the sheet or this macro and try again.", _
             vbCritical, "Fatal Error!"
    End If

End Function

With advice from Tim Williams, I removed On Error Resume Next and On Error Goto 0, they were unnecessary.

Upvotes: 2

Related Questions