excel - 为什么选择范围变量时会发生变化？

Question

这个问题在这里已经有了答案:





VBA Range variable gone after cut

(2 个回答)


2年前关闭。




最初选择的单元格存储在 rngStart最后重新选择，这样用户就不会被宏带走。但是，存储在 rngStart 中的范围变化。看来是自己。它最终成为粘贴操作发生的范围。

Sub Macro2()
    Application.ScreenUpdating = False

    Dim rngStart 'The variable I'm struggling with
    Dim ws As Worksheet

    Set rngStart = Selection 'Store original selection
    Set ws = ActiveSheet

    Selection.Cut
    'Find an empty cell in column B
    For Each cell In ws.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    ActiveSheet.Paste 'Upon executing this line, rngStart changes to the cell being pasted to
    rngStart.Select 'Supposed to return to the originally selected range

    Application.ScreenUpdating = True
End Sub

Answer 1

将其保存为字符串。

Sub Macro2()

    Application.ScreenUpdating = False

    Dim rngStart As String 'The variable I'm struggling with
    Dim ws As Worksheet

    rngStart = Selection.Address 'Store original selection
    Set ws = ActiveSheet

    Selection.Cut
    'Find an empty cell in row B
    For Each cell In ws.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    ActiveSheet.Paste 'Upon executing this line, rngStart changes to the cell being pasted to
    Range(rngStart).Select  'Supposed to return to the originally selected range

    Application.ScreenUpdating = True

End Sub