VBA代码F5/F8执行正常但按钮触发时跳过部分代码的问题求助

嘿，我来帮你排查这个头疼的问题！你遇到的这种情况在VBA里挺常见的——手动按F5或者逐行调试（F8）时代码跑的全、结果对，但绑到按钮上触发就快得离谱，还丢了关键逻辑，对吧？先帮你理清楚现状：

现象梳理 #

• 正常执行（F5/F8）：耗时约20秒，流程全走通：
  • 解除Balances2工作表保护
  • 从Todays Bals表匹配数据，给Balances2插入新行
  • 自动填充B列到新行末尾
  • 给新行的C列到最后一列空单元格填N/A
  • 重新保护工作表并弹出完成提示
• 按钮触发执行：仅耗时2秒，结果错误：
  • 前半部分（解除保护、插入新行）正常
  • 后半部分B列自动填充、空单元格填N/A的逻辑完全没生效
  • 最后保护表和弹窗依然正常

你的原始代码 #

Sub ChkNewAccs()

Dim wb As Workbook, wsToday As Worksheet, wsBalances As Worksheet

Dim lastRow As Long, r As Long, arr, v

Dim newrow As Long

Dim Rng As Range

Dim FillRng As Range

Application.ScreenUpdating = False

Worksheets("Balances2").Protect Contents:=False

Set wb = ThisWorkbook

Set wsToday = wb.Worksheets("Todays Bals")

Set wsBalances = wb.Worksheets("Balances2")

lastRow = wsToday.Cells(Rows.Count, "A").End(xlUp).Row

arr = wsToday.Range("B2:B" & lastRow).Value

For r = 1 To UBound(arr, 1)

v = arr(r, 1)

If Len(v) > 0 Then

If IsError(Application.Match(v, wsBalances.Columns(1), 0)) Then

With wsBalances

newrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(newrow, 1).EntireRow.Insert

End With

wsBalances.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = v

End If

End If

Next r

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("B2").Select

Selection.AutoFill Destination:=Range("B2:B" & lastRow)

Call AddNANewAccs

End Sub

Sub AddNANewAccs()

Dim lastRow As Long, lastCol As Long

Dim Rng As Range

Dim WholeRng As Range

Dim Cell As Range

With Worksheets("Balances2")

Set Rng = .Cells

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

lastCol = Worksheets("Balances2").Cells(2, Columns.Count).End(xlToLeft).Column

Set WholeRng = .Range(.Cells(1, "C"), .Cells(lastRow, lastCol))

For Each Cell In WholeRng

Cell.Value = IIf(IsEmpty(Cell), "N/A", Cell.Value)

Next

End With

Worksheets("Balances2").Protect Contents:=True

Application.ScreenUpdating = True

MsgBox "Completed"

End Sub

问题根源分析 #

核心问题出在没有明确指定Range/Rows/Columns的父工作表，并且依赖了Select/Selection这种不稳定的操作：

1. 比如lastRow = Range("A" & Rows.Count).End(xlUp).Row、Range("B2").Select这些代码，都没指定是哪个工作表的Range。手动执行时，你可能正好在Balances2表，但按钮触发时，活动工作表可能是别的表（比如Todays Bals），导致代码拿错了行号，甚至在错误的表上执行AutoFill，自然看起来像是“跳过了代码”。
2. Select操作在ScreenUpdating=False的情况下很容易失效，因为Excel不会更新界面状态，活动单元格可能根本没切换到你要的位置。

修正后的代码 #

我把所有模糊的对象引用都明确了，还去掉了不靠谱的Select/Selection：

Sub ChkNewAccs()
    Dim wb As Workbook, wsToday As Worksheet, wsBalances As Worksheet
    Dim lastRow As Long, r As Long, arr, v
    Dim newrow As Long
    
    ' 先处理异常，确保屏幕更新能恢复
    On Error GoTo Cleanup
    
    Application.ScreenUpdating = False
    
    Set wb = ThisWorkbook
    Set wsToday = wb.Worksheets("Todays Bals")
    Set wsBalances = wb.Worksheets("Balances2")
    
    ' 解除保护
    wsBalances.Protect Contents:=False
    
    ' 读取Todays Bals的数据
    lastRow = wsToday.Cells(wsToday.Rows.Count, "A").End(xlUp).Row
    arr = wsToday.Range("B2:B" & lastRow).Value
    
    ' 匹配并插入新行
    For r = 1 To UBound(arr, 1)
        v = arr(r, 1)
        If Len(v) > 0 Then
            If IsError(Application.Match(v, wsBalances.Columns(1), 0)) Then
                With wsBalances
                    newrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                    .Cells(newrow, 1).EntireRow.Insert
                    .Cells(newrow, 1).Value = v ' 直接赋值，不用Offset，更可靠
                End With
            End If
        End If
    Next r
    
    ' 自动填充B列，明确指定wsBalances
    With wsBalances
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("B2").AutoFill Destination:=.Range("B2:B" & lastRow)
    End With
    
    ' 调用填充N/A的子程序
    Call AddNANewAccs

Cleanup:
    ' 确保屏幕更新恢复，即使出错也不会卡住
    If Application.ScreenUpdating = False Then
        Application.ScreenUpdating = True
    End If
    If Err.Number <> 0 Then
        MsgBox "执行出错：" & Err.Description, vbExclamation
    End If
End Sub

Sub AddNANewAccs()
    Dim lastRow As Long, lastCol As Long
    Dim WholeRng As Range
    Dim Cell As Range
    
    With ThisWorkbook.Worksheets("Balances2")
        ' 明确指定父表的Rows/Columns
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set WholeRng = .Range(.Cells(1, "C"), .Cells(lastRow, lastCol))
        
        ' 填充N/A
        For Each Cell In WholeRng
            If IsEmpty(Cell) Then Cell.Value = "N/A"
        Next
        
        ' 重新保护
        .Protect Contents:=True
    End With
    
    MsgBox "Completed"
End Sub

关键修改点说明 #

1. 所有Range/Rows/Columns都绑定到指定的工作表对象：比如wsToday.Rows.Count、.Cells(.Rows.Count, 1)，彻底避免依赖活动工作表。
2. 去掉Select/Selection：直接用Range.AutoFill方法，不需要选中单元格，更稳定高效。
3. 添加错误处理：确保即使代码出错，屏幕更新也能恢复，还能提示错误信息。
4. 优化新行赋值逻辑：插入新行后直接给newrow行的A列赋值，比用Offset更可靠。

额外建议 #

以后写VBA代码时，尽量养成明确指定所有对象父级的习惯，别依赖活动表、活动单元格这种不确定的状态，这样不管是手动执行还是按钮触发，代码的行为都是一致的。

备注：内容来源于stack exchange，提问作者Adezzz