CODEWITHSUNDEEP
vbaexcel
Ben
Ben

Reputation: 43

VBA Loop Debugging - Next Without For

Essentially Im trying to copy and insert a certain range of cells on the second sheet as the program loops through a range of cells on the first sheet as long as the cells arent empty. I need the copy and insert range to change to the newly copy and inserted cells for each loop. Any help would be much appreciated

Private Sub CommandButton1_Click()

    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Integer
    Dim j As Integer

    For i = 12 To 24
        Set ws = ThisWorkbook.Sheets("Input")
        With ws
            If Not IsEmpty(Cells(i, 2)) Then
            For j = 10 To -2
                Set ws = ThisWorkbook.Sheets("Budget Output #2")
                With ws
                    Set rng = .Range("Cell(5,i-j):Cell(17,i-j+1)")
                    rng.Copy
                    rng.Offset(0, 2).Insert Shift:=xlToRight
                    rng.Offset(0, 2).ColumnWidth = 20
                    Application.CutCopyMode = False
            Next j
    Next i
            End If
                End With
        End With

End Sub

Upvotes: 1

Views: 106

Answers (3)

Davesexcel
Davesexcel

Reputation: 6984

You have two ws variables Possibly start your code out right.

  Dim ws As Worksheet, sh As Worksheet
    Set ws = Sheets("Budget Output #2")
    Set sh = Sheets("Input")

Upvotes: -1

Chrismas007
Chrismas007

Reputation: 6105

You do not need the With statements for ONE line. This will be much cleaner. Also with two sheets, you should use TWO sheet variables. Finally, I cleaned up your Range(Cells, Cells) syntax. Although, this will still not work because of your For j = 10 to -2. To move backwards, you have to use a Step -#.

Private Sub CommandButton1_Click()

    Dim wsIn As Worksheet, wsOut As Worksheet
    Dim rng As Range
    Dim i As Integer
    Dim j As Integer

    Set wsIn = ThisWorkbook.Sheets("Input")
    Set wsOut = ThisWorkbook.Sheets("Budget Output #2")    

    x = 2
    For i = 12 To 24
        If Not IsEmpty(wsIn.Cells(i, 2)) Then
            Set rng = wsOut.Range("B:C")
            rng.Copy
            rng.Offset(0, x).Insert Shift:=xlToRight
            rng.Offset(0, x).ColumnWidth = 20
            Application.CutCopyMode = False
            x = x + 2
        End If
    Next i

End Sub

Upvotes: 2

Alex
Alex

Reputation: 1642

I will let you figure out the answer. Here is the correct structure:

For i ....  
    For j ...  
       with ws
       end with
    next j
next i

Upvotes: 1

Related Questions