Excel 2 For in 1-CodePudding

I bumped into a problem as i tried to add 2 values to be searched and its seems something is wrong with my code.

The part is that I added 2 For each , but i think it's not how it suppose to go.

I have 2 words : PMC ( column F) and PRM ( Column C) . If they match in sheet Main Data, then copy that row and paste to Second Data.


Sub Copyrow()

Dim c As Range

Dim D As Range

Dim j As Integer

Dim Source As Worksheet

Dim Target As Worksheet

Dim lRow As Long

Set Source = ActiveWorkbook.Worksheets("Main DATA")

Set Target = ActiveWorkbook.Worksheets("Second Data")

Lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row   1

j = Cells(Rows.Count, 2).End(xlUp).Row

For Each c In Source.Range("F1:F20000")

For Each D In Source.Range("C1:C20000") ' Second ( FOR ) for addition search word.

If c = "PMC" & D = "PRM" Then

Source.Range("A" & c.D.Row, "O" & c.D.Row).copy

Target.Range("A" & j, "O" & j).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _

Operation:=xlNone, SkipBlanks:=False, Transpose:=False

j = j   1

End If

Next D

With Range("H1:H5000")

.NumberFormat = "General"

.Value = .Value

End With

End Sub

CodePudding user response：

This is untested, the approach is to do a For over the rows and check the cell value of column C and F in the row i.

Sub Copyrow()
   Dim j As Long
   Dim Source As Worksheet
   Dim Target As Worksheet
   Dim lRow As Long

   Set Source = ActiveWorkbook.Worksheets("Main DATA")
   Set Target = ActiveWorkbook.Worksheets("Second Data")

   lRow = Source.Cells(Source.Rows.Count, "F").End(xlUp).Row   1

   j = Target.Cells(Target.Rows.Count, 2).End(xlUp).Row

   Dim i As Long
   For i = 1 to lRow
       If Source.Cells(i, 6).Value = "PMC" And Source.Cells(i,3).Value = "PRM" Then
           Source.Range(Replace("A#:O#","#",i)).Copy
           Target.Range(Replace("A#:O#","#", j).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           j = j   1
       End If
   Next i

   With Target.Range("H1:H5000")
       .NumberFormat = "General"
       .Value = .Value
   End With
End Sub