在列Vba中查找范围中的最小值和最大值

I have been trying to find the min and max of a column and Cannot seem to get my code to work correctly. I have tried if statements, for loops and cannot seem to make it work. I have also been using application.worksheetfunction.min/max and have not been able to get anything to work.

我一直试图找到列的最小值和最大值,似乎无法让我的代码正常工作。我已经尝试过if语句,for循环并且似乎无法使其工作。我一直在使用application.worksheetfunction.min / max并且无法获得任何工作。

Sub MinMax()
    Dim xmax As Double
    Dim xmin As Double
    Dim TableRow As Integer

    For i = 2 To lastrow   
        If cells("i,11").Value < cells(i + 1, 11).Value Then
            xmin = cells(i, 11).Value
            cells(3, 16).Value = xmin
        End If

        If cells(i, 11).Value > cells(i + 1, 11).Value Then
            cells(2, 16).Value = xmax
        End If
    Next i
End Sub

1 个解决方案

#1

Try this:

Sub MinMax()

    Dim xmax As Double
    Dim xmin As Double

    Dim r As Range

    Set r = Range("K2:K" & Rows.Count)
    xmin = Application.WorksheetFunction.Min(r)
    xmax = Application.WorksheetFunction.Max(r)
End Sub