excel - 获取范围内前 3 个数字的单元格引用（Excel VBA）

Question

我在 Excel 中有一个带有数字的单元格范围（比如说A1:Z1），我想获得三个最高的数字。回答我在这里找到的这部分问题 -查找范围内的最高值和后续值

但我也想获得这些值的单元格引用。

firstVal = Application.WorksheetFunction.Large(rng,1)
secondVal = Application.WorksheetFunction.Large(rng,2)        
thirdVal = Application.WorksheetFunction.Large(rng,3)

标签： excelvba

Answer 1

获取值后，尝试循环遍历范围并将范围变量分配给这些值。然后打印范围变量的地址：

Sub TestMe()

    Dim firstVal As Double
    Dim secondVal As Double
    Dim thirdVal As Double
    Dim rng As Range
    Set rng = Worksheets(1).Range("A1:B10")

    With Application
        firstVal = Application.WorksheetFunction.Large(rng, 1)
        secondVal = Application.WorksheetFunction.Large(rng, 2)
        thirdVal = Application.WorksheetFunction.Large(rng, 3)
    End With

    Dim myCell As Range
    Dim firstCell As Range
    Dim secondCell As Range
    Dim thirdCell As Range

    For Each myCell In rng
        If myCell.Value = firstVal And (firstCell Is Nothing) Then
            Set firstCell = myCell
        ElseIf myCell.Value = secondVal And (secondCell Is Nothing) Then
            Set secondCell = myCell
        ElseIf myCell.Value = thirdVal And (thirdCell Is Nothing) Then
            Set thirdCell = myCell
        End If
    Next myCell

    Debug.Print firstCell.Address, secondCell.Address, thirdCell.Address

End Sub

进行检查firstCell Is Nothing以确保在有多个顶级变量的情况下，将第二个分配给 secondCell。例如，如果范围如下所示：

那么前 3 个单元格将是A2, A3, A1.