vba 选择前 10 个最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8762018/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select the top 10 maximum values
提问by rjk
I need help writing a loop that finds the 10 highest values in column "F". For each of the 10 highest values that are selected, I want to paste that value (as well as the associated values in column C, D, and E) in another spreadsheet.
我需要帮助编写一个循环，在“F”列中找到 10 个最高值。对于所选的 10 个最高值中的每一个，我想将该值（以及 C、D 和 E 列中的关联值）粘贴到另一个电子表格中。
Thanks
谢谢
回答by chris neilsen
The Aggregatefunction is designed to ignore error values (among other things). Here's a SUBAggregateand Largeto get a threashold to triggger your copy code
该Aggregate函数旨在忽略错误值（除其他外）。这是一个SUBAggregateandLarge来获得一个阈值来触发你的复制代码
Sub GetTop10(r As Range)
Dim v As Variant
Dim t As Variant
Dim i As Long
' 14 = function LARGE
' 6 = ignore error values
' 10 = get 10'th largest value
t = Application.WorksheetFunction.Aggregate(14, 6, r, 10)
v = r
For i = 1 To UBound(v, 1)
If Not IsError(v(i, 1)) Then
If v(i, 1) >= t Then
' copy r.cells(i,-2).resize(1,4) to your other sheet
End If
End If
Next
End Sub
