vba - 选择另一个工作表VBA后变量出错

Question

以下是我一直在编写的代码。但是，当我从同一工作簿中选择另一个工作表时，我意识到出现了问题。

变量ShiftName在经过下面代码中的Sheets("Cash").Select时似乎发生了变化。

我相信 ShiftName 的列从工作表“ShiftRoster”的“B”更改为“Cash”的“C”，这导致我的 ShiftName 输出为错了。

我想看看有什么办法可以解决这个问题吗？

Sub Testing()
    Sheets("Shift Roster").Select

    Range("A1").Select
    Cells.Find("LEAVE").Activate
    r1 = ActiveCell.Row

    Dim ShiftRowName As Integer
    Dim ShiftColName As String: ShiftColName = "B"
    Dim ShiftColLeave As String: ShiftColLeave = "E"
    Dim ShiftName As String
    Dim ShiftReason As String

    Dim CashRowName As Integer
    Dim CashColName As String: CashColName = "C"
    Dim CashColLeave As String: CashColLeave = "H"
    Dim CashName As String
    Dim CashLeave As String

    ShiftRowName = r1 + 1

    Do While Cells(ShiftRowName, 1) <> ""
        ShiftName = Cells(ShiftRowName, ShiftColName)
        ShiftReason = Cells(ShiftRowName, ShiftColLeave)

        If ShiftName = "" Or IsEmpty(ShiftName) Then
            Exit Do
        Else
            'SOMETHING WENT WRONG FROM HERE ONWARDS
            Sheets("Cash").Select

            Range("C1").Select
            Cells.Find("Name").Activate
            r2 = ActiveCell.Row

            CashRowName = r2 + 1

            Do While Cells(CashRowName, 1) <> ""
                CashName = Cells(CashRowName, CashColName).Value

                If CashName = "" Or IsEmpty(CashName) Then
                    Exit Do
                Else
                    MsgBox ShiftName
                    End If
                CashRowName = CashRowName + 1
            Loop

            End If
        ShiftRowName = ShiftRowName + 1
    Loop

End Sub

Answer 1

限定您的范围/单元格方法:

而不是

ShiftReason = Cells(ShiftRowName, ShiftColLeave)

使用

ShiftReason = Sheets("Shift Roster").Cells(ShiftRowName, ShiftColLeave)

因此您的代码确切知道您所指的是哪张表。如果不限定您的范围，它将假定您引用的是 ActiveSheet 对象。