CODEWITHSUNDEEP
vbaexcel
Gene
Gene

Reputation: 3

Copy certain cells from a row below when 2 conditions are both met

I'm really new to VBA and I only get by with copying codes from solutions on this site but for this one I can't find a similar problem.

I want to copy only certain cells (not whole rows) when column A and B aligns as "apple" with "cat". I also want to delete that row I copied from.

the plan illustrated

My code so far:

Sub animals() 
Dim rcnt As Long
rcnt = Worksheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To rcnt
    If Range("B" & i).Value = "cat" And ("A" & i) = "apple" Then
        Range("C" & i).Offset(1, 0).Copy
        Range("D" & i).Offset(1, 0).Copy
    End If
Next i

End Sub

Upvotes: 0

Views: 43

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

For i = 1 To rcnt
    If Range("B" & i).value = "cat" And ("A" & i) = "apple" Then
        Range("C" & i).value = Range("C" & i).Offset(1, 0).value
        Range("D" & i).value = Range("D" & i).Offset(1, 0).value
    End If
Next i

You were copying the data, just not pasting it anywhere. This method will skip the .Copy (skipping the clipboard), and just sets the values equal.

Upvotes: 1

Related Questions