listbox vlookup codes based on another worksheet

Question

i am sort of novice with VBA. i am trying to work with 2 listboxes, listbox1 (.lstdatabase) and listbox2 (.lstdatabase1). what i am trying to do is, when i click update cost button, selected rows from listbox1 (.lstdatabse) transfer to listbox2 (.lstdatabase1). while doing this it only transfers column 1 to 4 from listbox1 as required. I have manage to work above by suing codes. Now, I am struggling to populate listbox2 (.lstdatabase1) column 5 (this value is from worksheet (Cost)) based on value reference to column 4 in listbox2 (.lstdatabase1). Codes I have as below, Private Sub cmdcostupdates_Click() With UserForm1.lstdatabase1

Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("cost")

    .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(5, (UserForm1.lstdatabase1.ListCount - 1)) = Application.WorksheetFunction.VLookup(.List(3, i), Sheets("sh").Range("A1:G1000"), 7, False)

Can someone help to correct code for vlookup? below code gives me error.

UserForm1.lstdatabase1.Column(5, (UserForm1.lstdatabase1.ListCount - 1)) = Application.WorksheetFunction.VLookup(.List(3, i), Sheets("sh").Range("A1:G1000"), 7, False)

jay6939 jay6939 · Accepted Answer · 2020-07-13T05:34:38

Found the code, 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

listbox vlookup codes based on another worksheet

1 Answers