Looping incorrectly Excel VBA

Question

I'm trying to run a code that will search through a column, find keywords, then copy and paste those rows into another sheet. Unfortunately, when I run the code step-by-step I can see that the first time it attempts to copy and paste a row, it copies the active cell and pastes that value across the row in the next sheet, and disregards the "If Then" statement searching for the keywords. After it pastes the active cell value it works fine and pastes the correct rows, but I can't figure out why it pastes the active cell first.

Sub CompletedJob()

'Looks through the status column (N) of the Projects Overview table and moves them to Completed table, then deletes row from projects list
Dim Firstrow As Long
Dim lastRow As Long
Dim LrowProjectsOverview As Long

With Sheets("Projects Overview")
    .Select

    Firstrow = .UsedRange.Cells(1).Row
    lastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    For LrowProjectsOverview = lastRow To Firstrow Step -1
        With .Cells(LrowProjectsOverview, "N")
            If Not IsError(.Value) Then
                If ((.Value = "Complete - Design") Or (.Value = "P4P") Or (.Value = "Ready for Setup")) Then .EntireRow.Select

    Selection.Copy
                Range("A600:Q600").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

                If Sheet9.Range("B2").Value = "" Then
                Sheet9.Range("A2:Q2").Value = Sheet1.Range("A600:Q600").Value
                Sheet1.Range("A600:Q600").ClearContents

                Else

                Sheet9.Range("B2").EntireRow.Insert
                Sheet9.Range("A2:Q2").Value = Sheet1.Range("A600:Q600").Value
                Sheet1.Range("A600:Q600").ClearContents
                Sheet9.Range("B2:Q2").Interior.Color = xlNone
                Sheet9.Range("B2:Q2").Font.Bold = False
                Sheet9.Range("B2:Q2").Font.Color = vbBlack
                Sheet9.Range("B2:Q2").RowHeight = 14.25

            End If

            If Sheet9.Range("B2").Value = "" Then
               Sheet9.Range("B2").EntireRow.Delete

            End If

    If ((.Value = "Complete - Design") Or (.Value = "P4P") Or (.Value = "Ready for Setup")) Then .EntireRow.Delete

            End If
        End With
    Next LrowProjectsOverview
End With

End Sub

When you loop through you are not selecting anything, therefore you will copy the current active cell. It is best not to use `Selection` in your code. Instead you should use the variable you are looping through. — Darrell H, Jul 28 '17 at 20:44
WHen your `If ((.Value = "Complete - Design") Or (...` evaluates to `False`, you're *still* copying whatever is the current selection... — David Zemens, Jul 28 '17 at 20:45
do not use _select ... selection_ ... the `... EntireRow.Select ... Selection.Copy` should be `... EntireRow.Copy` ..... `Range("A600:Q600").select ....` should be `Range("A600:Q600").PasteSpecial ....` BTW which sheet contains the _Range("A600:Q600")_ ?? — jsotola, Jul 28 '17 at 20:50
Please refer also to the comments I made to [Alister M](https://stackoverflow.com/users/7355139/alister-m) when he posted [nearly the same code](https://stackoverflow.com/q/45336700/6535336) a couple of days ago. — YowE3K, Jul 28 '17 at 23:31

PalimPalim · Answer 1 · 2017-08-01T07:58:28.540

I tried to recreate your problem. My code is not a direct solution for you. You need to adapt it to your problem. This is important because this is how you learn to code.

I tried my very best to comment my code as heavily as possible. I am not referencing sheets, please add this since you are trying to copy from one sheet to another.

I don't need any select statement.

This is my vba code.

Option Explicit

Sub SearchKeyandCopy()
Dim LastLine As Long
Dim i As Long
Dim j As Long
'Find Number of Rows in Status column (column D)
LastLine = Columns("D").Find("*", , , , xlByColumns, xlPrevious).Row

'Set row where it should start pasting
j = 2

'Iterate over all cells with status
For i = 2 To LastLine
    'Check if the Keyword (Key) is in Status column
    'InStr returns the position and 0 if not found
    'code checks if position is different from 0
    If InStr(Cells(i, "D").Value, "key") <> 0 Then
        'Copy values
        Range(Cells(i, "A"), Cells(i, "D")).Copy Destination:=Range(Cells(j, "F"), Cells(j, "I"))
        'increase counter for where to paste
        j = j + 1
    End If
Next i

End Sub

This is how it looks before running the code

This is how it looks after running the code

Looping incorrectly Excel VBA

1 Answers1