Help! Find info then copy and paste
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. |
Help! Find info then copy and paste
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 "Simon" wrote: 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. |
All times are GMT +1. The time now is 11:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com