How to find double data type in range.find

user_10195756 0
Viewed 48

Input:

enter image description here

Sub RangeFindWithDoubleType()
    Dim strResult As range
    
    With ActiveSheet.range("A2:A11")
        On Error Resume Next
        
        Set strResult = .Find(range("D1")).Address
        
        If strResult Is Nothing Then
            MsgBox "Value not found"
        Else
            MsgBox strResult
        End If
    End With
    
    range("D2") = strResult
End Sub

When double type data is searched using find function, the obtained result is Nothing.

How to find the address for double data type cell?

range find double vba excel
1 Answers

First Occurrence of a Number in a Range Using Range.Find

Sub RangeFindWithDoubleType()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim srg As Range: Set srg = ws.Range("A2:A11") ' Source Range
    Dim cCell As Range: Set cCell = ws.Range("D1") ' Criteria Cell
    Dim dCell As Range: Set dCell = ws.Range("D2") ' Destination Cell
    
    Dim cValue As Variant: cValue = cCell.Value
    
    Dim sCell As Range ' First Found Cell in Source Range
    
    If VarType(cValue) = vbDouble Then ' is a number
        Set sCell = srg.Find( _
            cValue, srg.Cells(srg.Cells.Count), xlFormulas, xlWhole)
        If sCell Is Nothing Then ' number not found
            dCell.Value = Empty
        Else ' number found
            dCell.Value = sCell.Address(0, 0)
        End If
    Else ' not a number
        dCell.Value = Empty
    End If
    
End Sub
user_98140690
Related