Excel Forum

Code: Select all

Private Sub Test1_Click()
Dim CCenter As String
Dim rCode, rAll, r, r1 As Range

With Sheets("Existing")
    Set rAll = .Range("a:a").SpecialCells(xlCellTypeConstants)
    CCenter = .Range("A1")
End With

For Each r In rAll
    With Sheets("data")
            Set rCode = .Range("e:e").SpecialCells(xlCellTypeConstants)
        For Each r1 In rCode
                If r1.Value = CCenter Then
                    r.Offset(3, 1) = r1.Offset(, -4)
                End If
        Next r1
    End With
Next r
End Sub

Code: Select all

Dim CCenter As String
Dim rCode, rAll, r, r1 As Range
With Sheets("Existing")
    Set rAll = .Range("a:a").SpecialCells(xlCellTypeConstants)
    CCenter = .Range("A1")
End With
For Each r In rAll
    With Sheets("data")
            Set rCode = .Range("e:e").SpecialCells(xlCellTypeConstants)
        For Each r1 In rCode
                If r1.Value = CCenter Then
                r1.Offset(, -4).Copy
                     Sheets("Existing").Range("b" & Rows.Count) _
        .End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
                    'r.Offset(3, 1) = r1.Offset(, -4)
                End If
        Next r1
    End With
Next r