If and Do before EXCEL VBA cycle

Question

New to VBA, if someone can help me, what am I doing wrong here. Trying to start a cycle in such a way that it searches for a specific text, starts a cycle, and then stops at a certain point. The loops are such that I want them to copy some of the values ​​below in my sheet, which means it's 55. Im facing an error. IF block 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 

Answer 1

In addition to the problems that I mentioned in the comments on your post, if I understand you correctly, you want to loop on the cells in column A, find the first “Text1”, and then copy all the cells to line 55 and below until you find “Text2 " In this 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 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 

Answer 2

Indentation is the way forward, you have a for statement without next and if without 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