oops
my bad
remove the quotation marks from round the V
sorry

"Atishoo" wrote:

This brings us back to the question of how your triggering your code?
Your using a user form to enter data into sheet1 column A, so i assume you
have a "submit" button or OK or apply or something like that on your user
form for the end user to click when they have entered their data.
I would be thinking that your sub to lookup corresponding data from sheet 1
and populate sheet 2 with it should follow the sub that you have on this
command button.



"tomjoe" wrote:

Sorry, the code did't do the trick.

Let me be more clear in the explanation of the case.
I have adjusted the tables in sheet 1 and sheet 2 to get it more right (see
under).

In Sheet 2 it will steadily be added new Items. Now you can see that it's a
new item ,40, there (see table under). I then want the additional code to
look up when there is a new item in sheet 2 col A and the cell to the right
(col B) is NOT filled THEN go to Sheet 1 in the named range "Products" (see
table under) and get the corresponding Product an Variant thats on the same
row (as in this example the item 40) and paste these into the empty cells in
sheet 2 in the same row as 40 - in col B and C



Sheet 1
Named range A2:C5 = Products

Item ProdName Var
10 Product 1 Variant 1
20 Product 2 Variant 2
30 Product 3 Variant 1
40 Product 4 Variant 2


Sheet 2
Item ProdName Var
10 Product 1 Variant 1
20 Product 2 Variant 2
10 Product 1 Variant 1
30 Product 3 Variant 1
40


I hope this was more precicely formulated.

Thank you for not giving up on me :-)