If and Do Until Loop EXCEL VBA

Question

New to VBA if someone could help me what im doing wrong here. Trying to run a loop such that it looks for a specific text, starts the loop then stops at a specific point. The loops is such that I want it to copy some values below in my sheet hence a is 55. Im facing the error Block IF without End If

Here is the code:

Private Sub CommandButton3_Click()
For y = 1 To 15 Step 5
Dim x As Double
Dim a As Double
x = 1
a = 55
If Cells(x, y).Value = "Text1" Then
Do Until Cells(x, y).Value = "Text2"
Cells(a, y) = Cells(x, y).Value
Cells(a, y + 1) = Cells(x, y + 1)
x = x + 1
a = a + 1


Loop


End Sub

If you indent your code you'll find 2 errors: 1. If Cells(x, y).Value = "Text1" Then without End If, 2. For y = 1 To 15 Step 5 without Next y — Shai Rado
– Shai Rado, Commented Jan 24, 2017 at 17:33
If i was using if and then i thought i didnt need to use End if? — Isra Shaikh
– Isra Shaikh, Commented Jan 24, 2017 at 17:35
AFAIK the only time you don't need End If is if your If statement is on one line ...and even then, it's best practice to use a structred If statement. For example, this doesn't need End If: If myVal = 1 then Cells(1,1).Value = "Okay" all on one line. — BruceWayne
– BruceWayne, Commented Jan 24, 2017 at 17:55

Preston · Accepted Answer · 2017-01-24 17:35:35Z

4

Indenting is the way forward, you have a for statement with no next and an if with no End If:

Private Sub CommandButton3_Click()
    For y = 1 To 15 Step 5
        Dim x As Double
        Dim a As Double
        x = 1
        a = 55
        If Cells(x, y).Value = "Text1" Then
            Do Until Cells(x, y).Value = "Text2"
                Cells(a, y) = Cells(x, y).Value
                Cells(a, y + 1) = Cells(x, y + 1)
                x = x + 1
                a = a + 1
            Loop
        End If
    Next y
end sub

answered Jan 24, 2017 at 17:35

Preston

8,26511 gold badges64 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Isra Shaikh Over a year ago

Thanks tom! For some reason im not getting any output though. What i want is for my code to start from the first column, go through it and find Text1, which if it does copy the values onward to cells (a,1) until it reaches a cell that has Text2 after which it stops. Am i missing something?

Rdster Over a year ago

Should also move the Declarations prior to the For Loop.

Shai Rado Over a year ago

Also, x and a should be Long or Integer, not Double

Mark Balhoff Over a year ago

@IsraShaikh "from the first column, go through it and find Text1" Your code is only going to check the first row for Text1. There is no increment for x (the row) before you find Text1. So if Cells(1, 1) <> "Text1" you are going to immediately go to the next iteration of the outer loop (y = 6).

Mark Balhoff Over a year ago

@IsraShaikh Exactly what the other answer did. Add a loop around the if statement that increments x (the row) until you either find Text1 or reach the end of the column.

|

Shai Rado · Accepted Answer · 2017-01-24 19:09:03Z

2

Besides the issues I mentioned in the comments to your post, if I understood you correctly, you want to loop on cells at Column A, find the first "Text1", then copy all the cells to row 55 and below, until you find "Text2". If that's the case, try the code below :

Private Sub CommandButton3_Click()

Dim x As Long, y As Long
Dim a As Long
Dim LastRow As Long

With Worksheets("Sheet1") '<-- modify "Sheet1" to your sheet's name
    For y = 1 To 15 Step 5

        x = 1  '<-- reset x and a (rows) inside the columns loop
        a = 55 '<-- start pasting from row 55

        LastRow = .Cells(.Rows.Count, y).End(xlUp).Row
        While x <= LastRow '<-- loop until last row with data in Column y
            If .Cells(x, y).Value Like "Text1" Then
                Do Until .Cells(x, y).Value = "Text2"
                    .Cells(a, y).Value = .Cells(x, y).Value
                    .Cells(a, y + 1).Value = .Cells(x, y + 1).Value
                    x = x + 1
                    a = a + 1
                Loop
            End If
            x = x + 1
        Wend
    Next y
End With

End Sub

edited Jan 24, 2017 at 19:09

answered Jan 24, 2017 at 18:43

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

7 Comments

Isra Shaikh Over a year ago

Yes Thankyou that worked! How could i edit it to to check every fifth column? I added the following for loop but it didnt work. For y = 1 To 15 Step 5

Shai Rado Over a year ago

@IsraShaikh you want to do the exact copy paste for every 5th column? Until column 15?

Isra Shaikh Over a year ago

yes @ShaiRado So first it runs it on column 1, then column 6 etc until column 15

Shai Rado Over a year ago

@IsraShaikh test the edited code, should work like you wanted

Isra Shaikh Over a year ago

It works Thanks! Could you explain each of the code lines please! beginner with VBA could really use a helping hand.

|

Collectives™ on Stack Overflow

If and Do Until Loop EXCEL VBA

2 Answers 2

9 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related