Select cells according to row number in different sheet

[Helenf]

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

 

[Otto Moehrbach]

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]

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]

Helen
I have to see your file in order to find the error. If you wish, send me
your file or a small sample of your file. My email address is
(e-mail address removed). Remove the "extra" from this address. HTH
Otto