CODEWITHSUNDEEP
excelvba
LK.3
LK.3

Reputation: 25

VBA Loop and if statement issue

I have an issue with the following code. I want each cell with the value "long" in the column "U" to be copied in a new sheet. But the code I developed only retrieves the first result. It does stop after "U6". Can you please help me?

Sub reportcrea ()

    Worksheets("TLM").Select
    ActiveSheet.Range("U3").Select

    Do
        If ActiveCell.Value = "long" Then
           ActiveCell.EntireRow.Select
           Selection.Copy
           Sheets("report").Select
           Range("A" & Rows.Count).End(xlUp).Offset(1).Select
           ActiveSheet.Paste
           Application.CutCopyMode = False


      Else
          ActiveCell.Offset(1, 0).Select
      End If

      Loop Until IsEmpty(ActiveCell.Offset(1, 0))
End sub ()

Upvotes: 1

Views: 115

Answers (2)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

First up, End Sub shouldn't have trailing brackets. When I copied it into a module it highlighted an error straight away.

Your loop is using ActiveCell.Offset(1, 0).Select twice:

   If ActiveCell.Value = "long" Then
       ActiveCell.EntireRow.Select
       Selection.Copy
       ActiveCell.Offset(1, 0).Select 'first Offset
       Sheets("report").Select
       Range("A" & Rows.Count).End(xlUp).Offset(1).Select
       ActiveSheet.Paste
       Application.CutCopyMode = False
       Sheets("TLM").Select
       ActiveCell.Offset(1, 0).Select 'second Offset
  Else

so you're only looking at every second row after each "long" is found.

I tested your code on 10 contoguous "long" cells and got 5 back in the report sheet. I couldn't reproduce your U6 stop.

Upvotes: 1

Ahmad
Ahmad

Reputation: 12707

I found a bug in your code in this line:

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

Offset takes two parameters, so it should be something like this:

Range("A" & Rows.Count).End(xlUp).Offset(1,0).Select

Also, you should cancel CutCopy mode right after you paste what is in the clipboard:

ActiveSheet.Paste                 'Paste is done here
Application.CutCopyMode = False   'This is a good practice

See if that helps. Also, a screenshot of the TLM sheet would help us analyze the problem more accurately.

Upvotes: 2

Related Questions