excel - 如何在 range.find 中查找双数据类型

Question

输入:

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

使用find函数查找double类型数据时，得到的结果为Nothing。

如何找到双数据类型单元格的地址？

Answer 1

使用 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