Compile error For without Next - VBA code

Question

I am trying to write some VBA code that will search a worksheet called "01 Feb 19", column (T) and look for the word "Deployed", if it finds that word will then copy the entire row into a new worksheet called "deployed". I've added a For loop and I know I need to add a Next but I can't figure it out.

Here's my code so far.

Sub CopyRows()    
    Dim cell As Range
    Dim lastRow As Long
    Dim i As Long

    Dim wksh1 As Worksheet
    Set wksh1 = Worksheets("01 Feb 19")

    Dim wksh2 As Worksheet
    Set wksh2 = Worksheets("Deployed")

    lastRow = Range("A" & Rows.Count).End(xlUp).Row 'finds the last row
    i = 1

    For Each cell In wksh1.Range("T1:T" & lastRow) 'looks in T column until the last row
        If cell.Value = "Deployed" Then 'searches for word deployed
            cell.EntireRow.Copy wksh2.Cells(i, 1) 'copies entire row into Deployed work sheet      
        End If
End Sub

For a (possibly poor) example structure to help you along, have a look at this : https://stackoverflow.com/q/50776026/4961700 also look how the range is copied and delivered, which you don't seem to have yet. — Solar Mike, Feb 06 '19 at 09:21
Thank you! I will look now. That must be why I now get the message break in code. I know hardly anything about code and this is my first attempt so thank you for the help. — Jodie, Feb 06 '19 at 09:44

Tom · Accepted Answer · 2019-02-06T09:42:48.277

0

You're missing Next cell at the end of your For loop

Sub CopyRows()
    Dim cell As Range
    Dim lastRow As Long
    Dim i As Long

    Dim wksh1 As Worksheet
    Set wksh1 = Worksheets("01 Feb 19")

    Dim wksh2 As Worksheet
    Set wksh2 = Worksheets("Deployed")

    lastRow = Range("A" & Rows.Count).End(xlUp).Row 'finds the last row
    i = 1

    For Each cell In wksh1.Range("T1:T" & lastRow) 'looks in T column until the last row
        If cell.Value = "Deployed" Then 'searches for word deployed
            cell.EntireRow.Copy wksh2.Cells(i, 1) 'copies entire row into Deployed work sheet
            i = i + 1 ' <-- Added so that rows don't overwrite each other. Remove if it is intended to overwrite each row
        End If
    Next cell ' <-- Added
End Sub

edited Feb 06 '19 at 09:42

answered Feb 06 '19 at 09:19

Tom

9,725
3
31
48

You may want to look at incrementing your value of `i` in the loop as well. If this has solved your problem don't forgot to click the tick to mark it as solved on the left hand side of this answer – Tom Feb 06 '19 at 09:42
Thanks, I just realised that, the code works but I only get the last line. Fantastic! It's all working... what a buzz! Next step is to try to copy the headers over :) – Jodie Feb 06 '19 at 10:05

Compile error For without Next - VBA code

1 Answers1

Linked

Related