Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select cells according to row number in different sheet
Hi and thank you in advance
I have 2 different worksheets. The one worksheet (call it the catalogue for ease) has product codes and descriptions etc. The second one is a validation sheet with all the errors in the catalogue. This second validation sheet references the ROW NUMBER of the error in the catalogue instead of the product codes as we would like. Is there a way to find the product codes in these lines? We have a few hundred in a file that has 18000 product codes. Ideally I would like to identify the product codes with the errors and 'pull' them out so I can send them to suppliers. Please help as this will take ages to do! Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select cells according to row number in different sheet
Helen
This little macro will do what you want. I assumed the following: The sheets are named "Validation" and "Catalogue". The Catalogue sheet has product codes in Column A starting in A2 down. The Validation sheet has the row numbers in Column B starting in B2 down. The found product codes are placed in the Validation sheet in Column C starting in C2 down. You can write additional code in this macro to do whatever you want with the found product codes. Post back if you need more. Otto Sub FindPC() Dim rColB As Range Dim i As Range Sheets("Validation").Select Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp)) For Each i In rColB With Sheets("Catalogue") i.Offset(, 1) = .Cells(i.Value, 1).Value End With Next i End Sub "Helenf" wrote in message ... Hi and thank you in advance I have 2 different worksheets. The one worksheet (call it the catalogue for ease) has product codes and descriptions etc. The second one is a validation sheet with all the errors in the catalogue. This second validation sheet references the ROW NUMBER of the error in the catalogue instead of the product codes as we would like. Is there a way to find the product codes in these lines? We have a few hundred in a file that has 18000 product codes. Ideally I would like to identify the product codes with the errors and 'pull' them out so I can send them to suppliers. Please help as this will take ages to do! Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select cells according to row number in different sheet
Hi Otto
Thank you for your help. I have changed the 2 sheets so that the info is in the rows and columns that you used. However when I paste in the code it brings up the error: Application Defined or Object Defined Error. I have named the 2 tabs at the bottom of the sheet. In Sheet 1 now called Validation with the row numbers in Column B Starting in Cell2 Sheet 2 is now called Cataluge with the codes in Cloumn A starting in Cell2 Once again thank you for helping Otto "Otto Moehrbach" wrote: Helen This little macro will do what you want. I assumed the following: The sheets are named "Validation" and "Catalogue". The Catalogue sheet has product codes in Column A starting in A2 down. The Validation sheet has the row numbers in Column B starting in B2 down. The found product codes are placed in the Validation sheet in Column C starting in C2 down. You can write additional code in this macro to do whatever you want with the found product codes. Post back if you need more. Otto Sub FindPC() Dim rColB As Range Dim i As Range Sheets("Validation").Select Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp)) For Each i In rColB With Sheets("Catalogue") i.Offset(, 1) = .Cells(i.Value, 1).Value End With Next i End Sub "Helenf" wrote in message ... Hi and thank you in advance I have 2 different worksheets. The one worksheet (call it the catalogue for ease) has product codes and descriptions etc. The second one is a validation sheet with all the errors in the catalogue. This second validation sheet references the ROW NUMBER of the error in the catalogue instead of the product codes as we would like. Is there a way to find the product codes in these lines? We have a few hundred in a file that has 18000 product codes. Ideally I would like to identify the product codes with the errors and 'pull' them out so I can send them to suppliers. Please help as this will take ages to do! Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select cells according to row number in different sheet
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Still can select locked cells in protected sheet | New Users to Excel | |||
Select a certain number of cells in a row | Excel Discussion (Misc queries) | |||
is it possible to select and change the values of cells in a minimized excel spread sheet from vba? | Excel Worksheet Functions | |||
how can i select all the cells with same color on a sheet if there are multipale colors by vba code | Charts and Charting in Excel | |||
Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions |