vba 如何使用formular1c1而不在vba excel中定义范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17935230/
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 use formular1c1 without defining the range in vba excel?
提问by Jhenn3
My code is:
我的代码是：
Range("M1").Select
ActiveCell.FormulaR1c1 = "SUM(R[2]C:R[4157]C)"
My problem is what if I have more than 4157 rows. How do I ensure all rows for that column will be added up?
我的问题是如果我有超过 4157 行怎么办。如何确保将添加该列的所有行？
Thanks!
谢谢！
采纳答案by stenci
Try with:
尝试：
Range("M1").Select
ActiveCell.FormulaR1c1 = "=SUM(R[2]C:R[" & ActiveSheet.UsedRange.Rows.Count & "]C)"
EDIT:
编辑：
Added the =to the formula, so that Excel understands it is a formula. Otherwise it would just put the text in the cell.
添加=到公式中，以便 Excel 理解它是一个公式。否则它只会将文本放入单元格中。
回答by RowanC
As per Jaycal's comment try this:
根据 Jaycal 的评论，试试这个：
dim rowNum as integer
Range("M1").Select
rowNum=activesheet.cells(rows.Count,activecell.Column).end(xlUp).row
ActiveCell.FormulaR1c1 = "=SUM(R[2]C:R[" & rowNum & "]C)"
where using the cells notation means we can refer to row then column, row is taken as the max rows in worksheet, column is the active cell - which makes this a bit more reusable than referrring to range("M" & rows.count) given you've selected the cell anyway.
使用单元格表示法意味着我们可以先引用行然后引用列，行被视为工作表中的最大行，列是活动单元格 - 这使得它比引用范围（“M”和行数）更具可重用性反正你已经选择了单元格。
