vba 在 Excel 中定义单元格位置变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20251858/
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
Define Cell Location Variable in Excel
提问by Hugh_Kelley
I'm looking for a way to, instead of typing "ActiveCell.OffSet(1,1) over and over again in my vba code, define that as a variable, "x" and use that instead.
我正在寻找一种方法，而不是在我的 vba 代码中一遍又一遍地键入“ActiveCell.OffSet(1,1)”，而是将其定义为变量“x”并使用它。
I have to use the dim command to do this but I"m not sure what the data type would be.
我必须使用 dim 命令来执行此操作，但我不确定数据类型是什么。
Suggestions?
建议？
When I test it using the code below I get Runtime Error 1004.
当我使用下面的代码测试它时，我收到运行时错误 1004。
Private Sub CommandButton1_Click()
Dim i As Range
Set i = ActiveCell
ActiveSheet.Range(ActiveSheet.Range(i), ActiveSheet.Range(i).End(xlUp)).Select
End Sub
回答by Siddharth Rout
In response to your edit
回应您的编辑
Avoid the use of .Select/Activateand fully qualify your objects. INTERESTING READ
避免使用.Select/Activate并完全限定您的对象。有趣的阅读
Your code can be written as
你的代码可以写成
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
'~~> Change as applicable
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Set rng1 = ws.Range("A10")
Set rng2 = .Range(rng1, rng1.End(xlUp))
With rng2
Debug.Print .Address
'
'~~> Do something with the range
'
End With
End With
End Sub
If you still want to know what was wrong with your code then see this.
如果你仍然想知道你的代码有什么问题，那么看看这个。
You have already defined your range. You do not need to add ActiveSheet.Range()again. Your code can be written as
您已经定义了您的范围。您无需ActiveSheet.Range()再次添加。你的代码可以写成
Private Sub CommandButton1_Click()
Dim i As Range
Set i = ActiveCell
ActiveSheet.Range(i, i.End(xlUp)).Select
End Sub
EDIT
编辑
Followup from comments
来自评论的跟进
Was ActiveSheet.Range() actually problematic or just redundant? – user3033634 14 mins ago
ActiveSheet.Range() 是真的有问题还是只是多余的？– user3033634 14 分钟前
It is problematic. The default property of a range object is .Value
它是problematic。范围对象的默认属性是.Value
Consider this example which will explain what went wrong with your code
考虑这个例子，它将解释你的代码出了什么问题
Sub Sample()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Set rng = .Range("A1")
rng.Value = "Blah"
MsgBox rng '<~~ This will give you "Blah"
MsgBox rng.Value '<~~ This will give you "Blah"
MsgBox rng.Address '<~~ This will give you "$A"
MsgBox ws.Range(rng) '<~~ This will give you an error
'~~> Why? Becuase the above is evaluated to
'MsgBox ws.Range("Blah")
MsgBox ws.Range(rng.Address) '<~~ This will give you "Blah"
End With
End Sub
回答by rory.ap
Dim x As Range
Set x = ActiveCell.OffSet(1,1)
EDIT: in response to your comment:
编辑：回应您的评论：
Private Sub CommandButton1_Click()
Dim i As Range
Set i = ActiveCell
ActiveSheet.Range(i, i.End(xlUp)).Select
End Sub
