Results 1 to 6 of 6

Thread: copy to range problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default copy to range problem

    [code]
    Sub checkwood()
    Dim i As Integer
    Dim r2 As Integer
    Dim lastrow1 As Long
    Dim woodcoasters As Range


    lastrow1 = Cells(Rows.Count, 1).End(xlUp).Row




    For i = 2 To lastrow1

    r2 = 2
    If Cells(i, 3).Value = Cells(14, 3).Value Then
    Range("A" & i).Copy Range("j" & r2)

    r2 = 2 + 1

    End If

    Next i



    End Sub
    [code/]

    trying to get values to paste into column J if the cells in column C for the criteria. All values are pasting into J2 and not moving down
    ??

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copy to range problem

    Try it like
    Code:
    R2 = 2
    For i = 2 To Lastrow1
       If Cells(i, 3).Value = Cells(14, 3).Value Then
          Range("j" & R2).Value = Range("A" & i).Value
          R2 = R2 + 1
       End If
    Next i
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy to range problem

    Perfect. What was the main problem with original code? I need to declare r2 value outside of If Loop and?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copy to range problem

    There were two problems, this line
    Code:
    r2 = 2 + 1
    will simply make R2=3 every time through the loop, but that will then be overwritten because the first line line inside the loop set R2 back to 2.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy to range problem

    helpful as always. Thanks

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: copy to range problem

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •