Loop as if condition

Question

I have the following question.

In the below code i am trying to get my sub to run a loop after the if then statement, and then have the code go back to the first loop and start from the next i. In my below code,everything works fine, but when the condition in the first IF- statement is met, it starts the second loop, but then imidiately exits it again without running it.

So my question is, how do you make a loop after the then statement in a IF- statement?

Sub Sort3()

    Dim i As Integer
    Dim LastRow As Long
    Dim lenght As String
    Dim LastRow_2 As String
    Dim L_text As Variant
    Dim R_text As Variant
    Dim M_text As Variant

    ThisWorkbook.Sheets("EQ_CLEAN").Select

    LastRow = Range("G" & Rows.Count).End(xlUp).Row

    LastRow_2 = Range("J" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow

        lenght = Range("G" & i)

        If Len(lenght) = 25 Then

            L_text = Left(Range("A" & i), 12)
            R_text = Right(Range("A" & i), 12)

            For x = 2 To Last_row_2

                On Error Resume Next
                n = Worksheets("EQ_CLEAN").Range("D1:D6000").Cells.SpecialCells(xlCellTypeConstants).Count

                If L_text <> Sheets("EQ_CLEAN").Range("J" & x) Then

                    Sheets("EQ_CLEAN").Range(Cells(x, 1), Cells(x, 2)).Select
                    Application.CutCopyMode = False
                    Selection.Copy
                    Range("D" & (n + 1)).Select
                    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
                End If
            Next x
        End If
    Next i
End Sub

Comment out `On Error Resume Next` and see if you're getting run-time errors. — Comintern, Oct 26 '16 at 13:52
`.Select` should, by good VBA code writing practice, be avoided if possible. I'll [leave this here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for you to read through. It will help you in the long run if you plan to continue writing VBA. :) — Tyeler, Oct 26 '16 at 14:31

bobajob · Answer 1 · 2016-10-26T14:24:43.617

2

Always use Option Explicit at the beginning. That would have flagged up to you that you dim and set LastRow_2, and then try to use Last_row_2...

edited Oct 26 '16 at 14:24

answered Oct 26 '16 at 14:18

bobajob

1,192
6
12

score 0 · Answer 2 · edited May 08 '18 at 21:49

Code Review

I don't think your question applies to your problem after careful review of your code. You had many errors present which I've corrected for you below:

Option Explicit
Sub Sort3()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("EQ_CLEAN")
    Dim LastRow As Long, LastRow_2 As Long
    Dim lenght As String
    Dim L_text As String, R_text As String, M_text As String
    Dim n As Long, x As Long, i As Long

    LastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row

    LastRow_2 = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row

    For i = 2 To LastRow

        lenght = ws.Range("G" & i).Value

        If Len(lenght) = 25 Then

            L_text = Left(ws.Range("A" & i).Value, 12)
            R_text = Right(ws.Range("A" & i).Value, 12)

            For x = 2 To LastRow_2

                'On Error Resume Next 'You should never use this unless you know exactly which error
                'is popping up and why it can't be avoided. Usually this is for 1004 errors that occur
                'outside of excel... It's better to use proper error handling instead of skipping all errors.
                'You also never tell excel to recognize errors via On Error GoTo 0. I advise you stay away
                'from handling errors with these two statements.

                'don't make it a habit to assign rogue variables values
                n = ws.Range("D1:D6000").Cells.SpecialCells(xlCellTypeConstants).Count

                If L_text <> ws.Range("J" & x).Value Then
                    ws.Range(ws.Cells(x, 1), ws.Cells(x, 2)).Copy
                    ws.Range("D" & (n + 1)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                    Application.CutCopyMode = xlCopy 'this is the proper format to remove the marching ants
                End If
            Next x
        End If
    Next i
End Sub

I tried to put comments where changes were made.

I ran your code on a blank sheet and it doesn't throw any errors. See if it achieves what you're looking for.