使用VBA查找最接近的值

Sub NearestValue()
    Dim rng As Range
    Dim r As Range
    Dim Mx As Single
    Dim i As Long
    Dim target As Integer
    '要查找的值所在的单元格
    target = Range("D10").Value
    '要查找的区域
    Set rng = Range([B10], Range("B" & Rows.Count).End(xlUp))
    '结果区域
    rng.Offset(, 1).ClearContents
    Mx = Application.Max(rng)
    '遍历单元格并查找
    For Each r In rng
        If Abs(target - r) < Mx Then
            Mx = Abs(target - r)
            i = r.Row
        End If
    Next r
    Cells(i, 3) = "匹配"
End Sub