vba 使用宏获取Excel中列的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8565151/
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
Get maximum value of columns in Excel with macro
提问by ArVan
First of all I have no idea of writing macros in excel, but now I have to write a code for a friend. So here we go. In my excel sheet I have a table which holds some producers as columns and 12 months of the year as rows. In their intersecting cell it's written the amount of products produced by the producer during that month. Now I need to find maximum and minimum values of produced goods within each month and output the producers of that goods. I found a code for a similar problem, but I don't understand it clearly and it has errors. Here is the code:
首先我对用excel写宏一无所知，但现在我必须为朋友写一段代码。所以我们开始了。在我的 excel 表中，我有一个表格，其中将一些生产者作为列，将一年中的 12 个月作为行。在它们的交叉单元格中，写着生产者在该月生产的产品数量。现在我需要找到每个月内生产的商品的最大值和最小值，并输出该商品的生产者。我找到了一个类似问题的代码，但我不太明白，并且有错误。这是代码：
Sub my()
Dim Rng As Range, Dn As Range, Mx As Double, Col As String
Set Rng = Range(Range("A1"), Range("A6").End(xlUp))
ReDim ray(1 To Rng.Count)
For Each Dn In Rng
Mx = Application.Max(Dn)
Select Case Mx
Case Is = Dn.Offset(, 0): Col = "A"
Case Is = Dn.Offset(, 1): Col = "B"
Case Is = Dn.Offset(, 2): Col = "C"
Case Is = Dn.Offset(, 3): Col = "D"
End Select
ray(Dn.Row - 1) = Col
Next Dn
Sheets("Sheet2").Range("A2").Resize(Rng.Count) = Application.Transpose(ray)
End Sub
I get the following error:
Run-time error'9': Subscript out of range.So my question is, what does this error mean and what do I need to change in this code to work?
我收到以下错误：
Run-time error'9': Subscript out of range.所以我的问题是，这个错误是什么意思，我需要在这段代码中更改什么才能工作？
EDIT1: OK, now the error is gone. But where do I get the results?
EDIT1：好的，现在错误消失了。但是我从哪里得到结果呢？
EDIT2I know this line is responsible for inserting the results in specified place, but I cant see them after execution. What's wrong with that?
EDIT2我知道这一行负责在指定位置插入结果，但执行后我看不到它们。那有什么问题？
采纳答案by xQbert
Error means the array you are trying to access has not been defined to contain the ordinal you're looking for: For example Array 10 has positions 0-9 so if I try and access array(10) it would throw that error or array(-1) it would throw that error.
错误意味着您尝试访问的数组尚未定义为包含您要查找的序数：例如，数组 10 的位置为 0-9，因此如果我尝试访问数组（10），它会抛出该错误或数组（ -1) 它会抛出那个错误。
I can't remember if excel is 0 or 1 based arrays.
我不记得 excel 是基于 0 还是 1 的数组。
Possibly change
可能改变
ray(Dn.Row - 1) = Col
to
到
if dn.row-1<> 0 then ' or set it to <0 if zero based.
ray(Dn.Row - 1) = Col
end if
回答by Adam Ralph
You don't need VBA (a macro) to do this. It can be done using a worksheet formula.
您不需要 VBA（宏）来执行此操作。可以使用工作表公式来完成。
E.g.
例如
If your producers are P1,P2,P3,P4 and your sheet looks like this:-
如果您的制作人是 P1、P2、P3、P4 并且您的工作表如下所示：-
A B C D E F
+-------------------------------------------
1 | Month P1 P2 P3 P4 Top Producer
2 | Jan 5 4 3 2
3 | Feb 2 3 5 1
4 | Mar 6 4 4 3
...
...
The following formula placed in cells F2,F3,F4,... will pick out the top producer in each month.
放置在单元格 F2,F3,F4,... 中的以下公式将选出每个月的顶级生产商。
=INDEX($B:$E,MATCH(MAX(B2:E2),B2:E2,0))
Generally it's better to try and use built in Excel functionality where possible. Resort to VBA only if you really need to. Even if you were to use the top producer/month data for some other operation which is only possible in VBA, at least the top producer/month data derivation is done for you by the worksheet, which will simplify the VBA required for the whole process.
一般来说，最好尽可能尝试使用内置的 Excel 功能。仅在您确实需要时才使用 VBA。即使您将顶级生产者/月份数据用于其他一些只能在 VBA 中才能实现的操作，至少可以通过工作表为您完成顶级生产者/月份数据的推导，这将简化整个过程所需的 VBA .
Transposing a range can also be done using a worksheet formula by using the TRANSPOSE()function.
通过使用TRANSPOSE()函数，也可以使用工作表公式来完成范围的转置。
BTW - I'm not sure what you want to do if two producers have the same output value. In the VBA example in your question, the logic seems to be:- if two producers are joint top in a month, pick the first one encountered. The formula I've given above should replicate this logic.
顺便说一句 - 如果两个生产者的产值相同，我不确定你想做什么。在您的问题中的 VBA 示例中，逻辑似乎是：- 如果两个生产者在一个月内联合领先，则选择遇到的第一个。我上面给出的公式应该复制这个逻辑。
回答by ja72
I have used these functions quite extensively and they are very reliable and fast:
我已经非常广泛地使用了这些功能，它们非常可靠和快速：
Public Function CountRows(ByRef r As Range) As Integer
CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
End Function
Public Function CountColumns(ByRef r As Range) As Integer
CountColumns = r.Worksheet.Range(r.End(xlToRight), r).Columns.Count
End Function
Give it a reference (ex. "A2") and it will return the filled cells down, or the the right until and empty cell is found.
给它一个引用（例如"A2"），它将向下返回填充的单元格，或者向右返回，直到找到空单元格。
To select multiple sells I usually do something like
要选择多个销售，我通常会做类似的事情
Set r = Range("A2")
N = CountRows(r)
Set r = r.Resize(N,1)
