CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

For... To VBA loop is not ending?

I have the following loop written,

For X = 1 To N
    Rng.Offset(, -3).Resize(, 670).Copy
    Rng.Offset(1, -3).Insert Shift:=xlDown
Next X
i = i + N

Which is supposed to start on a row (defined by i), and make new rows based on what N is. If N is equal to 20, I want this code to make 20 copies, then move onto the next row. However, on row 1, N = 3, and copy/pasting just seems to happen over and over. Any suggestions?

For context, the entire the code is as follows:

Sub NuPubPrepare()

    Dim i As Long, k As Long, N As Long, Entry As Range, Rng As Range

    i = 2
    While i <= 400
        Set Entry = Range("K" & i)
        For k = Columns("K").Column To Columns("GB").Column Step 5
            Set Entry = Union(Entry, Cells(i, k))
        Next k
        Set Rng = Range("D" & i)
        N = Application.WorksheetFunction.CountA(Entry)

        If N = 1 Then
            i = i + 1
        Else
            For X = 1 To N
                Rng.Offset(, -3).Resize(, 670).Copy
                Rng.Offset(1, -3).Insert Shift:=xlDown
            Next X
            i = i + N 

        End If
    Wend
End Sub

So N will count the number of cells with data in them across a wide range (Every 5 cells from Ki to GBi), and I'm trying to make the script insert new lines based on this number.

Upvotes: 0

Views: 684

Answers (1)

JamTay317
JamTay317

Reputation: 1017

This will do as you ask. 

Sub test()
Dim rng As Range
Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Set rng = ws.Range("1:1")
    For i = 1 To 5
        rng.Offset(1).Insert Shift:=xlDown
        rng.Copy
        rng.Offset(1).PasteSpecial xlPasteValues
    Next i
End Sub

Upvotes: 1

Related Questions