Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and User form
Assuming that you are triggering this with a command button on your user form
and the value is enetered in a combobox on the user form something like this might work. havent tested this though. I would be tempeted to name your ranges rather than use ("a:a") though. Private Sub CommandButton1_Click() v = userform1.combobox1.value With Worksheets("Sheet1").Range("a:a") Set c = .Find(v, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do With Worksheets("sheet2").Range("a:a") Set d = .Find("", LookIn:=xlValues) If Not d Is Nothing Then firstAddress = d.Address Do d.Value = c.value d.Offset(0, 1).Value = c.offset(0,1).value d.Offset(0, 2).Value = c.offset(0,2).value Loop While d Is Nothing End If End With Loop While c Is Nothing End If End With End Sub This should place your results in the first blank cell in column A in sheet 2. Tell me how you get on. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and User form
Thank you Atishoo
Your code works fine when I try it on a test case, so you know your stuff. However when I try to incorporate it in my exicting user form code I am not experienced enough to get it to work. The code for the OK Button that I ALLREADY have is looping through col A in sheet 2 and putting the Item 10, 20, 30 or 40 in the first empty cell, so I do not need THAT function in the additional code. I only need for the additional code to find the last allready filled cell in col A and then LOOKING IT UP in sheet 1 and put the appropriate "ProdName" (produktname) and "Var" (variant) in the same row in col B and col C in sheet 2. (See the example table for sheet 1 and sheet 2 in my first post). I am sure this is not complicated for anyone with a certain experience, but thats obviously not where I am now. But I am certainly willing to learn. "Atishoo" wrote: Assuming that you are triggering this with a command button on your user form and the value is enetered in a combobox on the user form something like this might work. havent tested this though. I would be tempeted to name your ranges rather than use ("a:a") though. Private Sub CommandButton1_Click() v = userform1.combobox1.value With Worksheets("Sheet1").Range("a:a") Set c = .Find(v, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do With Worksheets("sheet2").Range("a:a") Set d = .Find("", LookIn:=xlValues) If Not d Is Nothing Then firstAddress = d.Address Do d.Value = c.value d.Offset(0, 1).Value = c.offset(0,1).value d.Offset(0, 2).Value = c.offset(0,2).value Loop While d Is Nothing End If End With Loop While c Is Nothing End If End With End Sub This should place your results in the first blank cell in column A in sheet 2. Tell me how you get on. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and User form
OK that would be similar only the other way round to the code I posted
earlier, am not sure how your triggering it but try something like this. With Worksheets("Sheet2").Range("a:a") Set c = .Find("", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do v=c.offset(-1,0).value With Worksheets("sheet1").Range("a:a") Set d = .Find("v", LookIn:=xlValues) If Not d Is Nothing Then firstAddress = d.Address Do c.offset(-1,1).value= d.offset(0,1).value c.offset(-1,2).value= d.offset(0,2).value Loop While d Is Nothing End If End With Loop While c Is Nothing end if end with Good luck please let me know how it goes |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and User form
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 :-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and User form
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 :-) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and User form
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 :-) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and User form
Thank you Atishoo
Your code works just fine. It was the quotation marks round the V that prevented the code to work as expected. "Atishoo" wrote: 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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function in user form | Excel Programming | |||
Date field in user form & Loading a user form on opening workbook | Excel Programming | |||
Creating Lookup User Form | Excel Programming | |||
Looking to create a simple user form with lookup | Excel Discussion (Misc queries) | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming |