时间戳

首页 > 解决方案 > 为什么选择范围变量时会发生变化？

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

问题描述

最初选择的单元格被存储在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

标签： excelvba

解决方案

将其保存为字符串。

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

推荐阅读