CODEWITHSUNDEEP
excelvba
Jon
Jon

Reputation: 15

Is there a way to reset the variable in the for loop back to 1 in an if statement inside the loop VBA

I am trying to get this to work so that the variable sheet_number that is used for the for loop goes back to 1 every time the conditions are met in the if statement inside the for loop but I am unsure if this happens, as the program doesn't fill in all the sheets like I want it to do. Below is my code.

Sub Luka_Koper()

Dim i As Integer, j As Integer, imena As Integer, sheet_number As Integer, n As Integer, x As Integer, y As Integer


n = 8
For sheet_number = 1 To Application.Sheets.Count
    If Sheets("ZBIR UR").Cells(n, 2).Value = Sheets(sheet_number).Cells(1, 1).Value Then

    i = 3
    j = 4
    x = n + 1
    y = n + 2
        For j = 4 To 32
            Worksheets("ZBIR UR").Cells(n, i).Copy
            Worksheets(sheet_number).Cells(j, 2).PasteSpecial Paste:=xlPasteFormulas
        
            Worksheets("ZBIR UR").Cells(x, i).Copy
            Worksheets(sheet_number).Cells(j, 3).PasteSpecial Paste:=xlPasteFormulas
        
            Worksheets("ZBIR UR").Cells(y, i).Copy
            Worksheets(sheet_number).Cells(j, 4).PasteSpecial Paste:=xlPasteFormulas            
        
        i = i + 1
        Next j
        
    sheet_number = 1
    n = n + 3
    
    Else
        If n > 500 Then
        Exit Sub
        End If
    End If
Next sheet_number

End Sub

Upvotes: 0

Views: 1736

Answers (3)

user8221156
user8221156

Reputation:

The question refers to "For...Next" loop that starts with loop var = 1

For i = 1 To n
    If [condition] = True Then
        i = 0 ' This will restart the loop at i = 1 from the next iteration
    Else
        ..... ' Whatever you would normally execute
    End if
Next

Adjust/modify this to your particular needs.

Upvotes: 0

Variatus
Variatus

Reputation: 14383

I think you have your logic set up the wrong way around, sort of putting the cart before the horse. You aren't picking data from "ZBIR UR" to match each tab. Instead, you are distributing all data from "ZBIR UR" to designated sheets. Therefore you shouldn't loop through all the sheets in the workbook (not the Application) but through all the rows in "ZBIR UR". Of course, I have no good proof for my assumption but below is the what the code would look like if I were right. You may like to try it.

Sub Luka_Koper()
    ' 114

    Dim Ws          As Worksheet                ' loop object: Worksheets
    Dim LookFor     As Variant                  ' Sheets("ZBIR UR").Cells(n, 2).Value
    Dim Rl          As Long                     ' last row in "ZBIR UR"
    Dim R           As Long                     ' loop counter: Rows

    With Worksheets("ZBIR UR")
        Rl = .Cells(.Rows.Count, 2).End(xlUp).Row
        For R = 8 To Rl Step 3
            LookFor = .Cells(R, 2).Value        ' don't ref sheet multiple times
            For Each Ws In ThisWorkbook.Worksheets
                If Ws.Name <> .Name Then        ' all sheets except "ZBIR UR"
                    If Ws.Cells(1, 1).Value = LookFor Then
                        Range(.Cells(R, 3), .Cells(R + 2, 29)).Copy
                        Ws.Cells(4, 4).PasteSpecial Paste:=xlPasteFormulas
                    End If
                End If
            Next Ws
        Next R
    End With
End Sub

Upvotes: 0

Dominique
Dominique

Reputation: 17493

I'm not sure if this works, but I would heavily disadvise it: a for-loop is known as a kind of counter: you know where it starts, that it proceeds one by one (or step by step) until the last value is reached.

If you want to change that value inside the loop itself, I would advise a while-loop instead, something like:

sheet_number = 1
do while sheet_number <= Application.Sheets.Count
   ...
   if <condition>
   then sheet_number = 1 ' IMPORTANT: THIS MIGHT CAUSE AN INFINITE LOOP!!!
   end if
   ...
   sheet_number = sheet_number + 1
loop

Upvotes: 1

Related Questions