Help! Find info then copy and paste

[Guest]

Afternoon all,

Could someone help with the following? Sorry it's a bit long!

In sheet1 'A1' I want to enter the 'Prod Code' and in A2 the 'type'
In sheet2 there is an array of data
A B C D
Prod Code Item Type Price
1234 Ham loose 1.39
1234 Ham prepacked 1.59
5678 Cheese Brie 1.99
5678 Cheese Stilton 3.29
[There are another 20000 rows like this not in order]

When the 'Prod Code' is entered on sheet1 A1, and 'type' in A2 I want it to
find the exact 'Prod Code' and 'type' and paste 'Prod Code' 'Item' 'Type'
'Price' in sheet1 B1

If anyone could help, it will save me a week's worth of copy and paste-ing!!

Thanks

Simon.

 

[Guest]

Well, this may not be the slickest way to do it, but I'm new to this. In
the Sheet1 macros I tried this and it worked:

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim iRow As Long
Dim ProductCode As String
Dim ProductType As String
'
With Sheets("sheet1")
ProductCode = .Cells(1, 1)
ProductType = .Cells(2, 1)
End With

With Sheets("sheet2")
For iRow = 1 To .Range("A1").SpecialCells(xlCellTypeLastCell).Row
If .Cells(iRow, 1) = ProductCode And .Cells(iRow, 3) = ProductType Then
Sheets("sheet1").Cells(1, 2) = .Cells(iRow, 1) & " " & .Cells(iRow,
2) & " " & .Cells(iRow, 3) & " " & .Cells(iRow, 4)
Exit Sub
End If
Next iRow
End With
'
Sheets("sheet1").Cells(1, 2) = "<Not Found>"
'
End Sub