I'm really close to getting this right. I'm sure this code looks like
a mess but it basically does this:

it looks in Sheet1 Column 1 for "MCC", if it finds it, the data in
Columns 2 and 3 are copied and pasted into a spreadsheet called
("Lookup.xls") where values are populated into Column 6, the values in
Column 6 are then pasted back into Column 4 of Sheet1.

The problem is that the data is pasted at the top of column 4 in
Sheet1, I need it to paste into the same row from which it was copied.
Can someone please help me with this?


Sub Get_Data()
Dim CP As Range
Dim iR As Range
Dim i As Long
Dim Wb As Workbook
Dim dRange1 As Range
Dim dRange2 As Range
Dim dRange3 As Range

Application.ScreenUpdating = False

With Worksheets("Sheet1")
Set CP = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
Set dRange3 = Cells(2, 4)
End With

i = 1

Set Wb = Workbooks.Open("A:\Lookup.xls")
Set dRange1 = Wb.Sheets(13).Range("B3")

For Each iR In CP
If UCase(Trim(iR.Value)) = "MCC" Then
iR.Cells(1, 2).Copy
dRange1.Cells(i, 1).PasteSpecial xlPasteValues, , False, False
iR.Cells(1, 3).Copy
dRange1.Cells(i, 2).PasteSpecial xlPasteValues, , False, False

i = i + 1

With Wb.Sheets(13)
Set dRange2 = .Range("F3", .Cells(Rows.Count, 6).End(xlUp))
End With

dRange2.Copy
dRange3.PasteSpecial xlPasteValues, , False, False

Application.ScreenUpdating = True

End If
Next
End Sub


Thanks again,
-- Dan