填充ComboBox

Question

我如何使这段代码变得越来越短？

'Sheets("Summary(FG)") ComboBox1 items
For b = 3 To Sheets("CustomerList").Cells(3, 2).SpecialCells(xlLastCell).row
    If Sheets("CustomerList").Cells(b, 2) <> "" Then
        Worksheets("Summary(FG)").ComboBox1.AddItem (Sheets("CustomerList").Cells(b, 2))
    Else
    End If
Next

'Sheets("Summary(RawMat)") ComboBox1 items
For a = 2 To Sheets("RawMatList").Cells(2, 2).SpecialCells(xlLastCell).Column
    If Sheets("RawMatList").Cells(2, a) <> "" Then
        Worksheets("Summary(RawMat)").ComboBox1.AddItem (Sheets("RawMatList").Cells(2, a))
    End If
Next

'Sheets("Summary(WIP)") ComboBox1 items
For c = 3 To Sheets("WIPList").Cells(3, 2).SpecialCells(xlLastCell).row
    If Sheets("WIPList").Cells(c, 2) <> "" Then
        Worksheets("Summary(WIP)").ComboBox1.AddItem (Sheets("WIPList").Cells(c, 2))
    End If
Next

For Each Worksheet In Worksheets
    Application.Goto Reference:=Range("A1"), Scroll:=True
Next Worksheet

Answer 1

要获得更快版本的代码，您可以将范围添加到数组中并循环通过，而不是循环遍历单元格。

例如：

Dim varray as Variant
varray = Sheets("CustomerList").Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value

for b = 1 to ubound(varray)
    If varray(b,1) <> "" Then
        Worksheets("Summary(FG)").ComboBox1.AddItem (varray(b,1))
    End If
next b

Answer 2

Combobox和Listbox具有List属性。可以将数组分配给List属性以快速填充控件。

Range对象的Value属性返回一个数组(如果范围是多单元的)。

这意味着可以在一行中将Value属性赋值给List属性。您只需小心列数匹配即可。

Sub FillFinishedGoods()

    Dim rFG As Range

    With Sheets("CustomerList")
        Set rFG = .Range("B3", .Cells(.Rows.Count, 2).End(xlUp))
    End With

    Sheets("Summary(FG)").ComboBox1.List = rFG.Value

End Sub