Vba loop range specific range

Question

I am designing a macro where by when it runs it looks for a value "2" in column C, and copies that row from cell A-C ONLY, into sheet2. The code I am working is not working. Please could you help me.

Sub LoopRange()

  Dim rCell As Range
  Dim rRng As Range

  Set rRng = Sheet1.Range("C1:C20")

  For Each rCell In rRng.Cells

    If rCell.Value = "2" Then
    Range(Cells(1, 1), Cells(3, 3)).Copy Sheets("Sheet2").Cells(1, 1)
    End If

  Next rCell

End Sub

When it runs it not picking anything or looping . But tested this With Sheets("Sheet1") .Range(.Cells(1, 1), .Cells(3, 3)).Copy Sheets("Sheet2").Cells(1, 1) End With "the copying from sheet 1 & pasting to sheet 2 works. But when i try make it loop through the sheet i get no result — kay
– kay, Commented Nov 13, 2014 at 20:51
Your copying the exact same range to the exact same location. I'm guessing this is what you want inside your if statement: Range(Cells(rCell.Row, 1), Cells(rCell.Row, 3)).Copy Sheets("Sheet2").Cells(rCell.Row, 1) — Automate This
– Automate This, Commented Nov 13, 2014 at 20:53
Thanks Runner, but i tried that code, was unsuccessful, cells(1,1),cell(3,3) was my defined range to be copied if the value 2 is found in column C. — kay
– kay, Commented Nov 13, 2014 at 20:58
Thanks runner the code you posted worked thanks alot , it seems like cant see it on the time line anymore . — kay
– kay, Commented Nov 13, 2014 at 21:10
I had removed it because I miss understood your requirement of copying the same defined range. I reposted it since it helped you. Regards — Automate This
– Automate This, Commented Nov 13, 2014 at 21:25

Automate This · Accepted Answer · 2014-11-13 20:57:19Z

1

Your current code is copying the exact same cells to the exact same location every time. Try this instead:

Sub test()
  Dim rCell As Range
  Dim rRng As Range
  Dim cnt As Long
  cnt = 1

  Set rRng = Sheet1.Range("C1:C20")

  For Each rCell In rRng.Cells
    If rCell.Value = "2" Then
        Range(Cells(rCell.Row, 1), Cells(rCell.Row, 3)).Copy Sheets("Sheet2").Cells(cnt, 1)
        cnt = cnt + 1
    End If
  Next rCell
End Sub

Sheet 1 test data:

enter image description here

Sheet 2 results:

enter image description here

answered Nov 13, 2014 at 20:57

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

Vba loop range specific range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related