Locating data from a list

[Guest]

I have Excel2003SP2. I am trying to write a quotation program. On Sheet1 I
need to fill in a product name in colum C. The data, about 1000 rows, is
stored on sheet2 with the item name in colum J and the price in colum N.
Colum K,L,M are blank. I would like to write a macro to do the following.
When I select a cell in colum C Sheet1, by clicking something or pressing a
macro shortcut key, sheet2 must be selected to give me an opportunity to
select the item. This item together with its price in colum N must then be
inserted into sheet1, the item in col C and the price in col G. I have tried
many things but can't get it to work. The only thing that did work was a
macro which did a copy and paste, but, I had to scroll up and down on the
item in col C sheet2 to find the one I wanted, this takes to long.

 

[Guest]

You say product name in column C and then on sheet2 you talk about item name.
It there anything that can be searched on in Sheet2 that contains the
product name or that could help in positioning to eliminate your problem of
scrolling.

 

[Guest]

Sorry Tom, product/item name is the same thing, let's call it ITEM name. Just
to re-cap. I want to select a cell, any row, in Sheet1 Col C which is blank,
then locate/copy an item in Sheet2 Col J and same row Col N (the price) then
paste/insert in selected cell Sheet1 Col C and price in Col G. Note on Sheet1
the Col D,E and F are blank.