none
Next Not Looping RRS feed

  • Question

  • I am trying to Find all rows that have numerical data in column J and copy and paste the rows into Sheet 2.  The first row is identified, copied and pasted, but it doesn't loop to additional rows.  This is what I have so far:

    Sub testIt()
    Dim r As Long, endRow As Long, pasteRowIndex As Long

    endRow = 6000
    pasteRowIndex = 1

    For r = 1 To endRow

    'MsgBox Range("J" & r).Value


        If IsNumeric(Range("J" & r).Value) And Not IsEmpty(Range("J" & r).Value) Then 'Found

                Rows(r).Select
                Selection.Copy
                Sheets("Sheet2").Select
                Rows(pasteRowIndex).Select
                ActiveSheet.Paste
                pasteRowIndex = pasteRowIndex + 1
                Sheets("Sheet1").Select
        End If
    Next r
    End Sub

    Wednesday, December 2, 2015 5:47 PM

Answers

  • Hi,

    The code works well for me. Did you get any error message?

    If not, please check the value in J2 to see whether it is an number. I also suggest that you debug the code line by line to see which line cause this issue.

    Please let us know if you still have the problem.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 3, 2015 5:37 AM
    Moderator

All replies

  • It works Perfectly here.

    Whats up exactly ?

    Wednesday, December 2, 2015 5:57 PM
  • The same tested and it works perfectly. Maybe to be safe you should add

    ....

    pasteRowIndex = 1
    Sheets("Sheet1").Select

    ...

    The ony reason I think is that the data  in column J is in reality not numeric .You can check it with ISNUMBER()

    • Proposed as answer by Malick F Wednesday, December 2, 2015 7:42 PM
    Wednesday, December 2, 2015 6:04 PM
  • Hi,

    The code works well for me. Did you get any error message?

    If not, please check the value in J2 to see whether it is an number. I also suggest that you debug the code line by line to see which line cause this issue.

    Please let us know if you still have the problem.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 3, 2015 5:37 AM
    Moderator