CODEWITHSUNDEEP
vbaexcel
Krynce
Krynce

Reputation: 77

EXCEL VBA using FOR LOOP that will disregard the step +1 LOOP COUNTER

I am in search of a solution that will disregard or take one step back from the loop counter. Here is my code for better understanding.

 Sub DownArrow5_Click()
Dim c As Integer
Dim copyFromRow As Integer

copyFromRow = 1

For c = 1 To 20
   If ActiveSheet.Rows(copyFromRow).Hidden = False And Range("A" & c & "").Value <> "" Then
   'start copy paste
    ActiveSheet.Range("A" & copyFromRow & ", C" & copyFromRow & "").Copy
    Sheets("Sheet2").Select
    ActiveSheet.Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False

    Else
        c = 'disregard the +1 for the loop

   End If
 Next c   
End Sub

I can't decrement the counter since it will have a negative (-) result thus returning an unending loop.

NOTE: I need to copy and paste 20 UNHIDDEN rows to sheet2. That is why I need to get the Max counter (20). This is just a brief code from what I am working on.

Upvotes: 2

Views: 2812

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Indeed, manipulating your loop counter in a For...Next loop probably isn't a good idea. Your code snippet isn't clear about why you would need to do this, but if you need to use the value of c - 1 somewhere in your Else block, either use c - 1 (without assigning c), or assign c - 1 to another variable:

Sub DownArrow5_Click()
    Dim c As Integer, d As Integer

    For c = 1 To 20
        If (condition) then
            'do stuff here

        Else
            d = c - 1
            'some more stuff here using d

        End If
    Next

End Sub

UPDATE

Now that you have edited your code with more details, I think you're looking for something like this:

While c <= 20

    If Not ActiveSheet.Rows(copyFromRow).Hidden _
       And Range("A" & c).Value <> vbNullString Then

        'do your stuff
        c = c + 1

    End If

Wend

Note that VB has several loop constructs that can work just as well - and any condition that evaluates to False at 20 iterations will do, so just use whatever you find more readable:

Do Until c = 21 ' until c = 20 would make it 19 iterations since c starts at 1
   ...
   c = c + 1
Loop

Do While Not c > 20
   ...
   c = c + 1
Loop

Upvotes: 3

David Zemens
David Zemens

Reputation: 53623

Use another type of loop:

c = 1
Do
    If (condition) Then
        'do stuff

         c = c+1  'increment your counter variable
    Else:
        'presumably do nothing, i.e., "Disregard" the loop.
        ' do NOT increment the counter variable in the Else block
    End If

Loop While Not c > 20

Upvotes: 3

Related Questions