Excel VBA：将单元格地址设置为变量“对象需要错误”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25394301/
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
Excel VBA: Setting a Cell Address to a variable "Object Required Error"
提问by CSAW
I want to set a cell address to a variable. And then use that variable to set a cell formula with a referable cell rngi.e. Someone should be able to double click the cell with the formula and see the cell reference of rngrather than the value of the cell rngin the formula. I have the following code:
我想将单元格地址设置为变量。然后使用该变量设置带有可引用单元格的单元格公式，rng即有人应该能够双击带有公式的单元格并查看单元格引用rng而不是rng公式中单元格的值。我有以下代码：
Dim rng As Range
Dim j As integer
j = 5
Set rng = ActiveSheet.Cells(j, 1).Address(False, False) ' get error here
ActiveSheet.Cells(1, 15).Formula = "=5*" & rng
I get this "Object Required Error". What am i doing wrong?
我收到此“对象所需错误”。我究竟做错了什么？
采纳答案by CSAW
Okay, so I believe my issues was that I misunderstood the type that ActiveSheet.Cells(j, 1).Address(False, False)is.
好的，所以我相信我的问题是我误解了它的类型ActiveSheet.Cells(j, 1).Address(False, False)。
ActiveSheet.Cells(j, 1).Address(False, False)is a String, so you will get an error if you set a Rangeto a String. Simply changing rngto a Stringwill solve my problem:
ActiveSheet.Cells(j, 1).Address(False, False)是一个String，所以如果你设置一个，你会得到一个错误Range的String。只需更改rng为 a 即可String解决我的问题：
Solution:
解决方案：
Dim rng As String
Dim j As integer
j = 5
rng = ActiveSheet.Cells(j, 1).Address(False, False) ' works now
ActiveSheet.Cells(1, 15).Formula = "=5*" & rng
回答by guitarthrower
Alternatively, in case you need the rngfor anything else, you could do the following
或者，如果您需要rng其他任何东西，您可以执行以下操作
Dim rng As Range
Dim j As integer
j = 5
Set rng = ActiveSheet.Cells(j, 1)
ActiveSheet.Cells(1, 15).Formula = "=5*" & rng.address
