for i = 1 skips a few spots

2019-09-16 16:10发布

I have a for loop that works but when i check the results there are some rows that got skipped.

Is there something that sticks out to anyone that could be the result of the loop not seeing every #.

It always seems to skip on the same row containing the digit 3 and one containing the digit 6. I will run other rows correctly with those same digits.

It should see a row containing "3 SHEETS" for example and copy that row and insert 2 rows below it and paste. So there are 3 duplicate rows that I end up renaming to Descriptions (SHEET 1) etc.

In my spreadsheet it runs on integers 2, 4, 3 (Then skips 2) runs on 13 (Then skips 3) and runs on 7 in that order, if that helps any. I have another macro that does some renaming and that hits all the "Sheets" words so I know its not a problem with an extra space or something.

Sub ExpandRows()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim aCell As Range
Dim i As Integer

Set ws = ThisWorkbook.Sheets("Drawing Index")

With ws
For i = 1 To 99
    Set aCell = .Columns(1).find(What:=i & " SHEETS", LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        aCell.EntireRow.Copy
        aCell.Resize(i - 1).Insert
    End If
Next
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

标签: excel vba
0条回答
登录 后发表回答