VBA Loop and if statement issue

Question

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 ()

If you were to complete this task manually, would you use Excel's built-in Autofilter function to grab all the "long"-matching rows at once and copy them all to the "report" Worksheet? — Dan Wagner
– Dan Wagner, Commented Aug 11, 2014 at 11:53
Hi Dan - Yes my idea was to add a second loop within the first one in order to perform another task afterwards. — LK.3
– LK.3, Commented Aug 11, 2014 at 12:02

Ahmad · Accepted Answer · 2014-08-11 11:57:50Z

2

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.

answered Aug 11, 2014 at 11:57

Ahmad

12.8k7 gold badges53 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LK.3 Over a year ago

Hi Ahmad, thanks for your help but my issue remains. I think the issue could be linked with the "ActiveSheet.Range("U3").Select " code as I only have the first result from the column "U". As I am new on this forum, I am not able to post the screeshot of the "TLM" sheet. sorry.

lowak Over a year ago

@user3929329 you can use any image-upload site and post a link to it.

Mark Fitzgerald Over a year ago

@Ahmad Range.Offset has two optional params: RowOffset and ColumnOffset. So Range("A" & Rows.Count).End(xlUp).Offset(1).Select is syntactically correct.

Mark Fitzgerald · Accepted Answer · 2014-08-11 13:55:31Z

1

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.

answered Aug 11, 2014 at 13:55

Mark Fitzgerald

3,0683 gold badges27 silver badges29 bronze badges

3 Comments

LK.3 Over a year ago

Hi Mark, I am looking to copy every row containing "long". "U6" was the first row where "long" was occuring. I insereted sevral "offset" function in order to try to go to the next "long" row.

Mark Fitzgerald Over a year ago

The logic used will stop at the first empty cell below a "long" cell.

LK.3 Over a year ago

My file is 100 rows of either "long" or "short" without any empty cell in between. But the below code only retreive the first row with "long" and stop looping afterwards. Sorry if I was a bit unclear.

Collectives™ on Stack Overflow

VBA Loop and if statement issue

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related