Find match from listbox item in different sheet and copy text to next column

[KestutisTower]

Hello everybody,
I have a userform with listbox1 and textbox1 and comandbutton1.

Listbox is populated by RowSource property with named range( in sheet1).its product names.
Sheet1

Book1
	A	B	C
1	ID	Product	Price
2	1	tv
3	2	radio
4	2	mp3
Sheet1

also, i have a sheet2 witch has same product names( but different order)
sheet2

Book1
	A	B
1	Product	Price
2	tv
3	radio
4	mp3
Sheet1

what i need to do is... when i select an item in listbox1 and enter value in textbox1, and press on comandbutton1(Enter price), textbox1 value to be copied to column C in sheet1 maching row,
and sheet2 column B maching row.

I can get first bit working with this code:

Private Sub Comandbutton1_Click()
 
Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
Dim lRw As Long
lRw = lstEile.ListIndex + 1

  With sh      
  
    .Cells(lRw, 3).Value = textbox1.Value
      
  End With
  textbox1.Value = ""
  
End Sub

but I can't get the second bit going. Please help!!

 

[RobP]

Hi,

I've added a few lines of code - not tested it myself, but see if it gets you close ?

Cheers
Rob

Private Sub Comandbutton1_Click()
 
Dim sh, sh2 As Worksheet
Dim lRw As Long
Dim Rng As Range

Set sh = ThisWorkbook.Worksheets("Sheet1")
Set sh2 = ThisWorkbook.Worksheets("Sheet2")
    

lRw = lstEile.ListIndex + 1

Product = sh.Range("B" & lRw).Value

  With sh
  
    .Cells(lRw, 3).Value = textbox1.Value
      
  End With
  textbox1.Value = ""
  
  
  Set Rng = Sheet2.Columns("A:A").Find(What:=Product, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Rng Is Nothing Then Exit Sub  'exit if no data found
    
    With sh2
    
        .Cells(Rng.Row, 2).Value = textbox1.Value
  
    End With
  
End Sub

 

[KestutisTower]

Hi,

I've added a few lines of code - not tested it myself, but see if it gets you close ?

Cheers
Rob

Private Sub Comandbutton1_Click()
 
Dim sh, sh2 As Worksheet
Dim lRw As Long
Dim Rng As Range

Set sh = ThisWorkbook.Worksheets("Sheet1")
Set sh2 = ThisWorkbook.Worksheets("Sheet2")
   

lRw = lstEile.ListIndex + 1

Product = sh.Range("B" & lRw).Value

  With sh
 
    .Cells(lRw, 3).Value = textbox1.Value
     
  End With
  textbox1.Value = ""
 
 
  Set Rng = Sheet2.Columns("A:A").Find(What:=Product, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Rng Is Nothing Then Exit Sub  'exit if no data found
   
    With sh2
   
        .Cells(Rng.Row, 2).Value = textbox1.Value
 
    End With
 
End Sub

Thank you RobP for quick response. The code worked perfectly. Just had to change

Set Rng = Sheet2.Column....

to

Set Rng = sh2.Column...

, because it required for object. Otherwise perfect fit. Thanks again.

 

[RobP]

Thats great .. sorry for my Typo Glad to have been able to help.

cheers
Rob