Excel宏在Debug中工作，但不能全面运行

问题描述

所以我有一个宏被设计为在工作表的打印区域中为每个分页插入4个标题行。当我在调试模式下逐步运行时，它将为每个分页插入正确的标题行，但是当它自己运行时，它似乎正在跳过零件。我已经添加了Sleeps和Debug.Prints，以确定出错的地方，我仍然无法弄清楚。

So I have a macro that is designed to insert 4 header rows for each page break in the sheet's print area. It will insert the correct header rows for every page break when I run through it step by step in debug mode, however when it is running by itself it seems to be skipping parts. I have added Sleeps and Debug.Prints in order to figure out where it is going wrong and I still cannot figure it out.

这是代码：

Sub InsertRowPageBreak()

    Dim WS As Worksheet
    Dim rng As Range
    Dim pb As Variant
    Dim Row As Integer
    Dim OffSet As Integer
    Dim InsertRow As Integer

    Set WS = ThisWorkbook.Worksheets(1)
    WS.Activate
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Dim i As Integer
    i = 1

    For Each pb In WS.HPageBreaks
        Debug.Print "Iteration: " & i
        i = i + 1

        Row = pb.Location.Row
        Range("A" & Row).Select
        Debug.Print "Page Break at Row: " & Row

        If (Range("A" & Row - 2).Value Like "*Date*") Then
            InsertRow = Row - 4
            Range("A" & InsertRow).Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
            Debug.Print "Inserting Page Break @ Row: " & InsertRow
        Else
            Sleep 150
            InsertRow = Row - 1
            Debug.Print "Inserting Row " & InsertRow
            If (Range("D" & InsertRow).Value Like "*Compliment*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("1:4").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 1"
            ElseIf (Range("D" & InsertRow).Value Like "*Complaint*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("5:8").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 2"
            ElseIf (Range("D" & InsertRow).Value Like "*Question*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("9:12").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 3"
            End If
            Sleep 250
        End If
        Sleep 250
    Next pb

End Sub 

当我在Debug模式下运行它时，Debug.Print打印出来

When I run it in Debug mode the Debug.Print prints out

Iteration: 1
Page Break at Row: 33
Inserting Row 32
Inserted Header 1

Iteration: 2
Page Break at Row: 66
Inserting Row 65
Inserted Header 1

Iteration: 3
Page Break at Row: 94
Inserting Row 93
Inserted Header 2

Iteration: 4
Page Break at Row: 119
Inserting Row 118
Inserted Header 3

当它由自己运行时

Iteration: 1
Page Break at Row: 33
Inserting Row 32
Inserted Header 1

Iteration: 2
Page Break at Row: 35
Inserting Row 34

Iteration: 3
Page Break at Row: 92
Inserting Row 91
Inserted Header 2

Iteration: 4
Page Break at Row: 94
Inserting Row 93

任何建议或者帮助将不胜感激。

Any suggestions or help would be greatly appreciated.

谢谢，
凯文

推荐答案

插入 Pagebreak 后，Excel需要重新定位，才能更新 HPageBreaks 集合。

After inserting a Pagebreak Excel needs to repaginate in order to update the HPageBreaks collection.

为了在代码运行时允许Excel执行此操作，请使用 DoEvents 代替 Sleep 的

In order to allow Excel to do this while the code is running, use DoEvents in place of your Sleep's

这篇关于Excel宏在Debug中工作，但不能全面运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！