vba 如何在VBA中获取行范围2:60和列范围D:H中单元格的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20968734/
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
How to get in VBA the max value of the cells in the row range 2:60 and column range D:H
提问by user3168477
I am having an Excel sheet with integer values in 60 rows and in 7 columns. First row is the commentaire, and first three columns are the commentaires as well (I have to ommit those).
我有一个 Excel 工作表，其中包含 60 行和 7 列的整数值。第一行是评论，前三列也是评论（我必须忽略那些）。
So, I would like to iterate through the row range 2:60 in column range 3:7 (D:H) to find the max value of the celles in a row. Answer goes to the celles in the column I.
因此，我想遍历列范围 3:7 (D:H) 中的行范围 2:60 以找到一行中单元格的最大值。答案转到 I 列中的单元格。
What I have (simplified view):
我有什么（简化视图）：
Test1 Test2 Test3 Test4 One 1 2 3 4 Two 5 6 7 8 Three 1 2 5 4 Four 1 7 3 4
What I would like to have:
我想要什么：
Test1 Test2 Test3 Test4 Max One 1 2 3 4 4 Two 5 6 7 8 8 Three 1 2 5 4 5 Four 1 7 3 4 7
If I create a macro manually, I receive this:
如果我手动创建宏，我会收到以下信息：
range("D2:I2").Select
range("I2").Activate
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"
range("D3:I3").Select
range("I3").Activate
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"
range("D4:I4").Select
range("I4").Activate
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"
Cool... but I would like to iterate through that with a loop.
很酷……但我想用循环来遍历它。
I tried this, but doesnt work:
我试过这个，但不起作用：
Sub max()
For num = 2 To 4
range("D&num:I&num").Select
range("I & num").Activate
ActiveCell.FormulaR1C1 = "=MAX(RC[-5]:RC[-1])"
Next num
End Sub
Any ideas ?
有任何想法吗 ？
采纳答案by Siddharth Rout
Simoco has already mentioned what the error in your code is. I would also add that you should avoid the use of .Activate. You might want to see THIS
Simoco 已经提到您的代码中的错误是什么。我还要补充一点，您应该避免使用.Activate. 你可能想看这个
The most simplest way would be to use this which doesn't use looping
最简单的方法是使用不使用循环的 this
Sub Sample()
With ThisWorkbook.Sheets("YourSheetName")
.Range("I2:I60").Formula = "=Max(D2:H2)"
End With
End Sub
