复合框引用列内容，错误，请帮助！

风油精

i75FytHG.rar (12.03 KB, 下载次数: 144)

2007-12-5 10:01 上传

点击文件名下载附件
复合框引用列内容，错误，请帮助！

想引用第7行以下内容，但附件却引用了整列。

Dim rst As New ADODB.Recordset
Private Sub UserForm_Initialize()
 On Error Resume Next
    cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no';Data Source=" & ThisWorkbook.FullName
    rst.Open "select DISTINCT f1 from [数据源$]", cnn
    arr = rst.GetRows
    rst.Close
    ComboBox1.Column = arr
    '这个f1的f代表什么？是行数吗？怎么改了之后不行了？？？？？？？？？？？？
   
    cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no';Data Source=" & ThisWorkbook.FullName
    rst.Open "select DISTINCT f2 from [数据源$]", cnn
    arr = rst.GetRows
    rst.Close
    ComboBox2.Column = arr
End Sub

yuanzhuping

不知你要的是否是去重复和空格？试试这样

Private Sub UserForm_Initialize()
    On Error Resume Next
    Dim s As New Collection, arr, rng As Range
    Dim a As Integer
    Dim i As Integer
    a = [a65536].End(xlUp).Row
    For Each rng In Range("A7:A" & a)
        If rng <> "" Then s.Add rng, CStr(rng)
    Next
    ReDim arr(1 To s.Count)
    For i = 1 To s.Count
        arr(i) = s(i)
    Next
    ComboBox1.List = arr
    ComboBox1.ListIndex = 0
End Sub

风油精

很好! 那再加多一个ComboBox2呢,怎么写上了?

yuanzhuping

QUOTE:

以下是引用风油精在2007-12-5 21:44:35的发言：
很好! 那再加多一个ComboBox2呢,怎么写上了?

同样的代码稍改一下再写一个。

风油精

QUOTE:

以下是引用yuanzhuping在2007-12-5 21:57:33的发言：

同样的代码稍改一下再写一个。

那这样怎么不行?

Private Sub UserForm_Initialize()
    On Error Resume Next
    Dim s As New Collection, arr, rng As Range
    Dim a As Integer
    Dim i As Integer
    a = [a65536].End(xlUp).Row
    For Each rng In Range("A7:A" & a)
        If rng <> "" Then s.Add rng, CStr(rng)
    Next
    ReDim arr(1 To s.Count)
    For i = 1 To s.Count
        arr(i) = s(i)
    Next
    ComboBox1.List = arr
  

 a = [b65536].End(xlUp).Row
    For Each rng In Range("B7:B" & a)
        If rng <> "" Then s.Add rng, CStr(rng)
    Next
    ReDim arr(1 To s.Count)
    For i = 1 To s.Count
        arr(i) = s(i)
    Next
    ComboBox2.List = arr

End Sub

yuanzhuping

Private Sub UserForm_Initialize()
 On Error Resume Next
    Dim s1 As New Collection
    Dim s2 As New Collection
    Dim rng1 As Range
    Dim rng2 As Range
    Dim arr1, arr2
    Dim a, b As Integer
    Dim i As Integer
    a = [a65536].End(xlUp).Row
    For Each rng1 In Range("A7:A" & a)
        If rng1 <> "" Then s1.Add rng1, CStr(rng1)
    Next
    ReDim arr1(1 To s1.Count)
    For i = 1 To s1.Count
        arr1(i) = s1(i)
    Next
    ComboBox1.List = arr1
 
    b = [b65536].End(xlUp).Row
    For Each rng2 In Range("B7:B" & b)
        If rng2 <> "" Then s2.Add rng2, CStr(rng2)
    Next
    ReDim arr2(1 To s2.Count)
    For i = 1 To s2.Count
        arr2(i) = s2(i)
    Next
    ComboBox2.List = arr2
End Sub

jdmlw

谢谢，不过好像还是一样的效果

mxwei

{:soso_e100:}好

冰山之剑

去重复值用字典最好了，简单实用