Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fit info into a no. of places (insert row, copy & paste) easily | Excel Worksheet Functions | |||
HOW DO I COPY & PASTE A SMALL INFO FM AN XL TO THE CELL COMMENT | Excel Worksheet Functions | |||
How to copy/paste info into the protected sheet | Excel Discussion (Misc queries) | |||
I need to merge lots of info, w/o copy paste? | Excel Worksheet Functions |