mardi 24 janvier 2017

If and DoUntil VBA code wont display output

Cant seem to figure out why my code is not showing output. New VBA programmer only know basics so any help would be helpful.

What I want is for Excel to start checking a specific column for a specific text1 and then start copying and pasting those values till it reaches text2. After that I want it to check the next fifth column in the same manner. If you could suggest modifications to my code. Without putting in a for loop for the column my code looks like this.

Private Sub CommandButton7_Click()

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

   y = 1 'starts with the first column

    x = 1  'first row
    a = 70 'this is the row where i want the data to be posted
            If Cells(x, y).Value = "text1" Then 'check first for specific text
            Do Until Cells(x, y).Value = "text2" 'stop here
                Cells(a, y).Value = Cells(x, y).Value 'copy that data to new row
                Cells(a, y + 1).Value = Cells(x, y + 1).Value 'and the column adjacent to it
                x = x + 1
                a = a + 1
            Loop
            Else: x = x + 1 'if not on that row then check the next row

        End If



 End Sub

Aucun commentaire:

Enregistrer un commentaire