CODEWITHSUNDEEP
vbaexcelexcel-formula
kay
kay

Reputation: 29

Vba loop range specific range

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

Upvotes: 0

Views: 398

Answers (1)

Automate This
Automate This

Reputation: 31394

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

Upvotes: 1

Related Questions