更新下拉列表循环(Updating drop down list loop)

更新下拉列表循环(Updating drop down list loop)

我正在尝试创建一个列表，该列表检查数据库中是否有所需名称，然后将该项添加到下拉列表中。 到目前为止我的代码工作，因为它检查名称，但然后覆盖列表中的任何以前的条目。 我如何更改，以便每次找到正确的数据时添加新的列表项？

While ThisWorkbook.Worksheets("Inventory Database").Range("A" & j).Value <> "" If ThisWorkbook.Worksheets("Inventory Database").Range("A" & j) = ThisWorkbook.Worksheets("Equipment Availability").Cells(1, i) Then dvList = ThisWorkbook.Worksheets("Inventory Database").Range("B" & j) '~~> Creates the list With Sheets("Equipment Availability").Cells(2, 2).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=dvList .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If j = j + 1 Wend

I'm trying to create a list which checks a database for the desired name and then adds that item to the drop down. The code I have so far works as in it checks the name but then overwrites any previous entries in the list. How can I alter so that it adds a new list item each time it finds the correct data?

While ThisWorkbook.Worksheets("Inventory Database").Range("A" & j).Value <> "" If ThisWorkbook.Worksheets("Inventory Database").Range("A" & j) = ThisWorkbook.Worksheets("Equipment Availability").Cells(1, i) Then dvList = ThisWorkbook.Worksheets("Inventory Database").Range("B" & j) '~~> Creates the list With Sheets("Equipment Availability").Cells(2, 2).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=dvList .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If j = j + 1 Wend

最满意答案

您只应在引用命名范围时创建一次验证列表。 如果在每个循环上展开命名范围（添加新值），则会自动更新验证列表。

You should only create the validation list once, while referencing a named range. If you expand the named range on every loop (adding the new value), then the validation list will automatically be updated.