Prev: Measure on the date different
Next: Adding Outlook signature to email created by macro
From: Atishoo on 31 Mar 2010 07:10
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.
From: tomjoe on 31 Mar 2010 11:37
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.
From: Atishoo on 31 Mar 2010 12:27
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
From: tomjoe on 1 Apr 2010 06:01
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 :-)
From: Atishoo on 1 Apr 2010 13:25
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 :-)
 |  Next  |  Last
Pages: 1 2
Prev: Measure on the date different
Next: Adding Outlook signature to email created by macro