excel - 在 Excel 中定义单元格位置变量

Question

我正在寻找一种方法，而不是在我的 vba 代码中一遍又一遍地键入“ActiveCell.OffSet(1,1)”，而是将其定义为变量“x”并改用它。

我必须使用 dim 命令来执行此操作，但我不确定数据类型是什么。

建议？

当我使用下面的代码对其进行测试时，我得到运行时错误 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

Answer 1

回应您的编辑

避免使用 .Select/Activate并完全限定您的对象。 INTERESTING READ

你的代码可以写成

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

如果您仍然想知道您的代码出了什么问题，请查看此内容。

您已经定义了您的范围。您无需添加 ActiveSheet.Range()再次。你的代码可以写成

Private Sub CommandButton1_Click()
    Dim i As Range

    Set i = ActiveCell

    ActiveSheet.Range(i, i.End(xlUp)).Select
End Sub

编辑

评论的跟进

Was ActiveSheet.Range() actually problematic or just redundant? – user3033634 14 mins ago

是 problematic .范围对象的默认属性是 .Value
考虑这个例子，它将解释你的代码出了什么问题

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$1"

        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