Select data that meet criteria from a table adding it to a combobox in userform VBA Excel

0 votes

Please be patient with me; I'm new to coding and am well over my head. I have an excel table with data. I want to use VBA to choose values (numbers) from the table's Active Accessions, first column, that are in the same row as names in the second column. The names correspond to those in a combination box (cmbxLatinName). The list of chosen numbers from column 1 should then be added as a result of all of this into a separate comboxbox (cmbxSourceAcc). This current code, which I spent hours searching for to understand and utilise, sounds nice, but I can't get the Datarw right; I can't 'dim' it. I was going to write the results into the third column of the table since I knew how to pull them into the combobox even though there is a way to get directly into the combo box that is too hard for me. I'm sure my project and code are ugly, but I'm learning and they do provide the outcomes when I can get them to function.

'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
    GetSourceAcc = Sheet5.ListObjects("Active_Accessions").ListColumns(3).DataBodyRange.Value
    Let Me.cmbxSourceAcc.List = GetSourceAcc
    Me.cmbxSourceAcc.ListIndex = 0

End Function

The line Dim Datarw As... I can't figure out the correct way to dim it so it breaks/debugs too for or if depending on how I dim it.

Mar 26 in Others by narikkadan
• 63,000 points

1 answer to this question.

0 votes

Fill Combo Box With Matches

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
answered Mar 26 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel: How to analyze data in a table that contains multivalue cells

 The below formula will create a unique ...READ MORE

answered Jan 10 in Others by narikkadan
• 63,000 points
0 votes
1 answer

Is it possible to get data from a webpage in real-time to an excel file?

The conventional method of obtaining data from ...READ MORE

answered Jan 17 in Others by narikkadan
• 63,000 points
0 votes
1 answer

How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

answered Mar 18 in Others by narikkadan
• 63,000 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,980 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
0 votes
1 answer
0 votes
1 answer
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP