我正在尝试在用户表单中创建一个下拉列表,以填充从位于同一工作簿中的列表中提取的唯一值。我想避免打印这个唯一值列表。我这里有两个问题:
这是我不成功的尝试(我希望从 H 列中的值中提取唯一值的集合):
Private Sub OSizeBox_Click()
Dim arr() As New Collection, a
Dim rng() As Range
Dim LRow As Long
LRow = Cells(Rows.Count, 2).End(xlUp).Row
rng() = Range("H3", "H" & LRow)
For Each a In rng
arr.Add Str(a), Str(a)
Next
OSizeBox.RowSource = arr()
End sub
运行此代码时,我收到以下错误:“编译错误:无效的限定符”,突出显示了arr
我的 For/Next 循环。
任何帮助或建议将不胜感激!先感谢您。
试试这个代码:
Option Explicit
Private Sub UserForm_Activate() 'starts when the form becomes active
Dim col As New Collection, rng As Range, a As Variant
With ThisWorkbook.Worksheets(1) 'your WB and WS
Set rng = .Range("H3", .Cells(.Rows.Count, "H").End(xlUp))
End With
On Error Resume Next ' error suppression if the key is not unique
For Each a In rng
col.Add a.Text, a.Text 'added only unique values
Next
On Error GoTo 0 ' disable error handling
For Each a In col
Me.OSizeBox.AddItem a 'add unique values from col
Next
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句