Populate listbox with Vlookup

J

jaym6939

New Member
Joined
Jun 26, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Hello VBA Guru,
i am newby to VBA.i have Excel file with 3 user forms (file attached). i have managed to work out most of functions by learning VBA through different forums and youtube. i am stuck to create below formula that will finish my form.
looking for help to create formulas for (i have tried many but nothing works)
1) copy selected items from listbox 1 (lstdatabase, userform3) to listbox 2 (lstdatabse1, useform1). i am able to copy required column from lstdatabase to lstdatabse1. however, i would like to add additional column to lstdatabse1(userform1) from different sheet (cost, cost1 , cost2) in workbook by referencing part number column 3 of lstdatabse1. i have tried Vlookup but somehow it not working (highlighted in red). please refer below code i used for this extercise. please suggest modification or provide your input to make it work.


Rich (BB code): 
Private Sub cmdcostupdates_Click()
With UserForm1.lstdatabase1

        .ColumnCount = 10
        .ColumnHeads = True
        .ColumnWidths = "40,60,60,60,60,100,100,250,80,80"


Dim i As Integer
For i = 0 To UserForm3.lstDatabase.ListCount - 1

    If UserForm3.lstDatabase.Selected(i) = True Then
     UserForm1.lstdatabase1.AddItem
     UserForm1.lstdatabase1.Column(0, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(0, i)
     UserForm1.lstdatabase1.Column(1, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(1, i)
     UserForm1.lstdatabase1.Column(2, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(2, i)
     UserForm1.lstdatabase1.Column(3, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(3, i)
     UserForm1.lstdatabase1.Column(4, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(4, i)
     'UserForm1.lstdatabase1.Column(6, (UserForm1.lstdatabase1.ListCount - 1)) = Application.WorksheetFunction.VLookup(UserForm1.lstdatabase1.Column(3, i), Sheets("cost").Range("A1:G1000"), 7, False)
     'UserForm1.txtcurrentprice3.Value = Application.WorksheetFunction.VLookup(UserForm1.lstdatabase1.Value("Selection.Row;3"), Sheets("cost").Range("A1:G1000"), 7, False)
    End If
Next i

  
    UserForm1.Show
End With

End Sub

2) once, lstdatabse1 populated with column 4 (from cost tab),5 (from cost1 tab) and 6 (from cost2 tab), i would like to click on particular row in lstdatabase1 and userform1 textbox should automatic fill from values.
UserForm1.txtcost.Value = Application.WorksheetFunction.VLookup(UserForm1.lstdatabase1.Value("Selection.Row;3"), Sheets("cost").Range("A1:G1000"), 7, False)
link for the file,
Login - Dropbox


thanks if advance for your time and help.
 
Last edited by a moderator:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
jaym6939 said:
i would like to add additional column to lstdatabse1(userform1) from different sheet
UserForm1.lstdatabase1.Column(6, (UserForm1.lstdatabase1.ListCount - 1)) =
Application.WorksheetFunction.VLookup(UserForm1.lstdatabase1.Column(3, i), Sheets("cost").Range("A1:G1000"), 7, False)
Click to expand...
In userform1.lstdatabase1 you added a new record, so the data in column 3 is not in i, it is in listcount -1.

Try thi:

VBA Code: 
Private Sub cmdcostupdates_Click()
  Dim i As Long, n As Long, f As Range
  
  With UserForm1.lstdatabase1
    .ColumnCount = 10
    .ColumnHeads = True
    .ColumnWidths = "40; 60; 60; 60; 60; 100; 100; 250; 80; 80"
    For i = 0 To UserForm3.lstdatabase.ListCount - 1
      If UserForm3.lstdatabase.Selected(i) = True Then
        .AddItem
        n = .ListCount - 1
        .Column(0, n) = UserForm3.lstdatabase.Column(0, i)
        .Column(1, n) = UserForm3.lstdatabase.Column(1, i)
        .Column(2, n) = UserForm3.lstdatabase.Column(2, i)
        .Column(3, n) = UserForm3.lstdatabase.Column(3, i)
        .Column(4, n) = UserForm3.lstdatabase.Column(4, i)
        Set f = Sheets("cost").Range("A:A").Find(.Column(3, n), , xlValues, xlWhole)
        If Not f Is Nothing Then
          .Column(6, n) = Sheets("cost").Range("G" & f.Row)
        End If
      End If
    Next i
    UserForm1.Show
  End With
End Sub
 
Upvote 0
DanteAmor said:
In userform1.lstdatabase1 you added a new record, so the data in column 3 is not in i, it is in listcount -1.

Try thi:

VBA Code: 
Private Sub cmdcostupdates_Click()
  Dim i As Long, n As Long, f As Range
 
  With UserForm1.lstdatabase1
    .ColumnCount = 10
    .ColumnHeads = True
    .ColumnWidths = "40; 60; 60; 60; 60; 100; 100; 250; 80; 80"
    For i = 0 To UserForm3.lstdatabase.ListCount - 1
      If UserForm3.lstdatabase.Selected(i) = True Then
        .AddItem
        n = .ListCount - 1
        .Column(0, n) = UserForm3.lstdatabase.Column(0, i)
        .Column(1, n) = UserForm3.lstdatabase.Column(1, i)
        .Column(2, n) = UserForm3.lstdatabase.Column(2, i)
        .Column(3, n) = UserForm3.lstdatabase.Column(3, i)
        .Column(4, n) = UserForm3.lstdatabase.Column(4, i)
        Set f = Sheets("cost").Range("A:A").Find(.Column(3, n), , xlValues, xlWhole)
        If Not f Is Nothing Then
          .Column(6, n) = Sheets("cost").Range("G" & f.Row)
        End If
      End If
    Next i
    UserForm1.Show
  End With
End Sub
Click to expand...
Thanks Dante Amor. This works like magic. i am trying to apply same logic to fill column 7,8,9 from different sheet in same workbook (cost1, cost2 & cost3). however it gives me errors (object required). can you pl guide me what should i do to correct it.
updated code i am using,
Private Sub cmdcostupdates_Click()
Dim i As Long, n As Long, f, X, Y As Range


With UserForm1.lstdatabase1
.ColumnCount = 10
.ColumnHeads = True
.ColumnWidths = "40; 60; 60; 60; 200; 100; 100; 250; 80; 80"
For i = 0 To UserForm3.lstDatabase.ListCount - 1
If UserForm3.lstDatabase.Selected(i) = True Then
.AddItem
n = .ListCount - 1
.Column(0, n) = UserForm3.lstDatabase.Column(0, i)
.Column(1, n) = UserForm3.lstDatabase.Column(1, i)
.Column(2, n) = UserForm3.lstDatabase.Column(2, i)
.Column(3, n) = UserForm3.lstDatabase.Column(3, i)
.Column(4, n) = UserForm3.lstDatabase.Column(5, i)
Set f = Sheets("cost").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)
Set X = Sheets("cost1").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)
Set Y = Sheets("cost2").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)

If Not f Is Nothing Then
.Column(5, n) = Sheets("cost").Range("I" & f.Row)
End If
If Not X Is Nothing Then
.Column(6, n) = Sheets("cost1").Range("I" & X.Row)
End If
If Not X Is Nothing Then
.Column(7, n) = Sheets("cost2").Range("I" & X.Row)
End If

End If
Next i
UserForm1.Show
End With
End Sub
 
Upvote 0
DanteAmor said:
In userform1.lstdatabase1 you added a new record, so the data in column 3 is not in i, it is in listcount -1.

Try thi:

VBA Code: 
Private Sub cmdcostupdates_Click()
  Dim i As Long, n As Long, f As Range
 
  With UserForm1.lstdatabase1
    .ColumnCount = 10
    .ColumnHeads = True
    .ColumnWidths = "40; 60; 60; 60; 60; 100; 100; 250; 80; 80"
    For i = 0 To UserForm3.lstdatabase.ListCount - 1
      If UserForm3.lstdatabase.Selected(i) = True Then
        .AddItem
        n = .ListCount - 1
        .Column(0, n) = UserForm3.lstdatabase.Column(0, i)
        .Column(1, n) = UserForm3.lstdatabase.Column(1, i)
        .Column(2, n) = UserForm3.lstdatabase.Column(2, i)
        .Column(3, n) = UserForm3.lstdatabase.Column(3, i)
        .Column(4, n) = UserForm3.lstdatabase.Column(4, i)
        Set f = Sheets("cost").Range("A:A").Find(.Column(3, n), , xlValues, xlWhole)
        If Not f Is Nothing Then
          .Column(6, n) = Sheets("cost").Range("G" & f.Row)
        End If
      End If
    Next i
    UserForm1.Show
  End With
End Sub
Click to expand...
Thanks Dante Amor. This works like magic. i am trying to apply same logic to fill column 7,8,9 from different sheet in same workbook (cost1, cost2 & cost3). however it gives me errors (object required). can you pl guide me what should i do to correct it.
updated code i am using,
Private Sub cmdcostupdates_Click()
Dim i As Long, n As Long, f, X, Y As Range


With UserForm1.lstdatabase1
.ColumnCount = 10
.ColumnHeads = True
.ColumnWidths = "40; 60; 60; 60; 200; 100; 100; 250; 80; 80"
For i = 0 To UserForm3.lstDatabase.ListCount - 1
If UserForm3.lstDatabase.Selected(i) = True Then
.AddItem
n = .ListCount - 1
.Column(0, n) = UserForm3.lstDatabase.Column(0, i)
.Column(1, n) = UserForm3.lstDatabase.Column(1, i)
.Column(2, n) = UserForm3.lstDatabase.Column(2, i)
.Column(3, n) = UserForm3.lstDatabase.Column(3, i)
.Column(4, n) = UserForm3.lstDatabase.Column(5, i)
Set f = Sheets("cost").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)
Set X = Sheets("cost1").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)
Set Y = Sheets("cost2").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)

If Not f Is Nothing Then
.Column(5, n) = Sheets("cost").Range("I" & f.Row)
End If
If Not X Is Nothing Then
.Column(6, n) = Sheets("cost1").Range("I" & X.Row)
End If
If Not X Is Nothing Then
.Column(7, n) = Sheets("cost2").Range("I" & X.Row)
End If

End If
Next i
UserForm1.Show
End With
End Sub
 
Upvote 0
It is working now. Made few chagnes
Private Sub cmdcostupdates_Click()
Dim i As Long, n As Long, f, f1 As Range
r As Rang

With UserForm1.lstdatabase1
.ColumnCount = 10
.ColumnHeads = True
.ColumnWidths = "40; 60; 60; 60; 200; 100; 100; 250; 80; 80"
For i = 0 To UserForm3.lstDatabase.ListCount - 1
If UserForm3.lstDatabase.Selected(i) = True Then
.AddItem
n = .ListCount - 1
.Column(0, n) = UserForm3.lstDatabase.Column(0, i)
.Column(1, n) = UserForm3.lstDatabase.Column(1, i)
.Column(2, n) = UserForm3.lstDatabase.Column(2, i)
.Column(3, n) = UserForm3.lstDatabase.Column(3, i)
.Column(4, n) = UserForm3.lstDatabase.Column(5, i)
Set f = Sheets("cost").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)
Set f1 = Sheets("cost1").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)
Set r = Sheets("cost2").Range("A4:I400").Find(.Column(4, n), , xlValues, xlWhole)

If Not f Is Nothing Then
.Column(5, n) = Sheets("cost").Range("I" & f.Row)
End If
If Not f1 Is Nothing Then
.Column(6, n) = Sheets("cost1").Range("I" & f1.Row)
End If
If Not r Is Nothing Then
.Column(7, n) = Sheets("cost2").Range("I" & r.Row)
End If

End If
Next i
UserForm1.Show
End With
End Sub
 
Upvote 0
I'm glad to hear that it works for you, thanks for commenting.(y)
 
Upvote 0
just on same form, i have number of parts populated under multi-select listbox. what i want to do is, when user select parts under list box and press action parts button (click event) based on selection under combo box Action it should change part and populate under part tracker sheet to respected column reference to change number under top of form.
i would like to change parts as per below,
if action combo box has,
RP= when user click on action selected parts, it should change 2nd last character of part end. Example (if part is at AAW, when action part click event happened, part should change to ABW and follow on until reaches to AZW.
RC= no change to part. same part End populate under part tracker sheet to respected column reference to change number under top of form.
DP= Delete this part for future reference. keeping in part tracer list but remove from list box from next change.

i have tried replace function and keep searching on web to get this on working. if you can help me that would be great.
below is image for your reference,
i have tried to upload file but due to size it is not allowing.
you can find at below link,
Part tracker test2.xlsm
thanks
 

Attachments

  • part action .JPG
    part action .JPG
    62.3 KB · Views: 7
Upvote 0
It seems to me that it is a new requirement, you should create a new thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
combining two procedures by selected last item in listbox or based on last highlighted cell
Replies
0
Views
546
abdelfattah
A
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
279
campbell1093
C
E
  • Question Question
Search function not finding rows containing the matching value
Replies
1
Views
678
GlennUK
G
P
  • Question Question
Data not copying fully into ListBox2
Replies
0
Views
321
Patriot2879
P
A
  • Solved
doesn't show data in listbox on form without any error in this code
Replies
6
Views
138
Abdo
A

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top