excel 从表中选择符合条件的数据，并将其添加到UserForm中的ComboBox

8xiog9wr 于 12个月前发布在其他

关注(0)|答案(1)|浏览(126)

我在Excel中有一个数据表。我想从表（Active_Accessions）的第一列中选择值（数字），这些值与第二列中的名称在同一行中。名称与ComboBox（cmbxLatinName）中的名称匹配。然后，所有这些操作的结果应该将第1列中的选定数字列表添加到不同的ComboBox（cmbxSourceAcc）中。
这段代码看起来不错，除了Datarw，我不能Dim了。我知道有一种方法可以直接进入ComboBox，但这太复杂了，所以我打算将结果写入表的第三列，我知道如何将它们拉入ComboBox。

'Match source accession to its sub-accession
Public Function GetSourceAcc()

    Dim Tbl As ListObject
    Dim r As Long
    Dim Datarw As
    
    Set Tbl = Sheet5.ListObjects("Active_Accessions")
    r = 1
    For Each Datarw In Tbl.ListRows
    If Datarw.ListColumns(2).DataBodyRange = Me.cmbxLatinName.Value Then
        Tbl.ListColumns(3).Offset(r, 0) = Tbl.ListColumns(1).Value
        r = r + 1
    End If
    Next
    
    GetSourceAcc = Sheet5.ListObjects("Active_Accessions").ListColumns(3).DataBodyRange.Value
    
    Let Me.cmbxSourceAcc.List = GetSourceAcc
    Me.cmbxSourceAcc.ListIndex = 0

End Function

excel

来源：https://stackoverflow.com/questions/75839003/select-data-that-meet-criteria-from-a-table-adding-it-to-a-combobox-in-a-userfo

1条答案

按热度按时间

nzk0hqpo1#

在组合框中填充匹配项

Sub GetSourceAcc()
    ' It is assumed that the ranges have at least 2 cells (rows) (each).

    Dim tbl As ListObject: Set tbl = Sheet5.ListObjects("Active_Accessions")
    
    ' Source Lookup (sl)
    Dim slrg As Range: Set slrg = tbl.ListColumns(2).DataBodyRange
    Dim srIndexes(): srIndexes = Application.Match(Me.cmbxLatinName.List, slrg, 0)
    ' 'srIndexes' holds the matching row indexes i.e. the numbers of the rows
    ' where each item from the combo box was found in the source lookup range.
    ' If an item was not found, an error value was returned (shouldn't happen).
    
    ' Source Return (sr)
    Dim srData(): srData = tbl.ListColumns(1).DataBodyRange.Value
    
    ' Write the matching values to an array.
    
    ' Destination (d)
    Dim dCount As Long: dCount = UBound(srIndexes)
    Dim dArr(): ReDim dArr(1 To dCount)
    
    Dim d As Long, n As Long
    
    For d = 1 To dCount
        If IsNumeric(srIndexes(d, 1)) Then
            n = n + 1
            dArr(n) = srData(srIndexes(d, 1), 1)
        End If
    Next d
    
    If n < dCount Then ReDim Preserve dArr(1 To n)
    
    'Debug.Print Join(dArr, ", ")
    
    ' Return the matching values in another combo box.
    
    Me.cmbxSourceAcc.List = dArr
    Me.cmbxSourceAcc.ListIndex = 0

    MsgBox "Combo box populated.", vbInformation

End Function

赞(0）回复(0）举报 12个月前

我来回答

excel 从表中选择符合条件的数据，并将其添加到UserForm中的ComboBox

1条答案

在组合框中填充匹配项

相关问题

热门标签

最新问答