时间戳

首页 > 解决方案 > 基于另一个工作表的列表框 vlookup 代码

arrays - 基于另一个工作表的列表框 vlookup 代码

问题描述

我是 VBA 的新手。我正在尝试使用 2 个列表框，listbox1 (.lstdatabase) 和 listbox2 (.lstdatabase1)。我想做的是，当我单击更新成本按钮时，从 listbox1 (.lstdatabse) 中选择的行转移到 listbox2 (.lstdatabase1)。在执行此操作时，它仅根据需要将第 1 列从 listbox1 传输到第 4 列。我已经通过起诉代码设法在上面工作。现在，我正在努力根据对 listbox2 (.lstdatabase1) 中第 4 列的值引用来填充 listbox2 (.lstdatabase1) 第 5 列（此值来自工作表 (Cost)）。我的代码如下，Private Sub cmdcostupdates_Click() With UserForm1.lstdatabase1

将 sh 调暗为工作表集 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)

有人可以帮助纠正 vlookup 的代码吗？下面的代码给了我错误。

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

标签： arraysvbalistboxvlookup

解决方案

找到代码，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

推荐阅读