Copying only one column of Userform ListBox data to single cell in separate spreadsheet with commas separating data

0 votes

I made a multicolumn ListBox with two columns where I can search for a customer name and have the results display customer part names in column 1 and associated component numbers in column 2. I want to be able to extract the entirety of column 2 (of part numbers) into a specific cell, J9, in my workbook's "New Profile Part Template" tab once a user finds a customer name.

Below is my code. I've looked through a lot of possibilities online, but I can't seem to locate any code that does what I want it to.

Below is a list of the current code; The data from ListBox Columns 1 and 2 are supposed to be pasted into cell J9 with commas separating the values, but instead, CommandButton1 pastes all of the data into cell J9:

Option Explicit
' Display All Matches from Search in Userform ListBox
Dim FormEvents As Boolean

Private Sub ClearForm(Except As String)

' Clears the list box and text boxes EXCEPT the text box
' currently having data entered into it

Select Case Except

    Case "FName"
        FormEvents = False
        LName.Value = ""
        Results.Clear
        FormEvents = True

    Case "LName"
        FormEvents = False
        FName.Value = ""
        Results.Clear
        FormEvents = True
        
    Case Else
        FormEvents = False
        FName.Value = ""
        LName.Value = ""
        Results.Clear
        FormEvents = True
        
    End Select

End Sub

Private Sub ClearBtn_Click()

ClearForm ("")

End Sub

Private Sub CloseBtn_Click()

Me.Hide

End Sub

Private Sub FName_Change()

    If FormEvents Then ClearForm ("FName")

End Sub

Private Sub LName_Change()

    If FormEvents Then ClearForm ("LName")

End Sub


Private Sub Results_Click()

End Sub

Private Sub SearchBtn_Click()
    Dim SearchTerm As String
    Dim SearchColumn As String
    Dim RecordRange As Range
    Dim FirstAddress As String
    Dim FirstCell As Range
    Dim RowCount As Integer

    ' Display an error if no search term is entered
    If FName.Value = "" And LName.Value = "" Then
        MsgBox "No search term specified", vbCritical + vbOKOnly
        Exit Sub
    End If

    ' Work out what is being searched for
    If FName.Value <> "" Then
        SearchTerm = FName.Value
        SearchColumn = "Service Part"
    End If

    If LName.Value <> "" Then
        SearchTerm = LName.Value
        SearchColumn = "Part Number"
    End If

    Results.Clear

        ' Only search in the relevant table column i.e. if somone is searching Service Part Name
        ' only search in the Service Part column
        With Sheet3.Range("Table1[" & SearchColumn & "]")

            ' Find the first match
            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)

            ' If a match has been found
            If Not RecordRange Is Nothing Then

            FirstAddress = RecordRange.Address
            RowCount = 0

            Do
            
                ' Set the first cell in the row of the matching value
                Set FirstCell = Sheet3.Range("A" & RecordRange.Row)
                
                ' Add matching record to List Box
                Results.AddItem
                Results.List(RowCount, 0) = FirstCell(1, 1)
                Results.List(RowCount, 1) = FirstCell(1, 2)
                
                RowCount = RowCount + 1
                
                ' Look for next match
                Set RecordRange = .FindNext(RecordRange)

                ' When no further matches are found, exit the sub
                If RecordRange Is Nothing Then
                    Exit Sub
                End If

            ' Keep looking while unique matches are found
            Loop While RecordRange.Address <> FirstAddress

        Else
        
            ' If you get here, no matches were found
            Results.AddItem
            Results.List(RowCount, 0) = "Nothing Found"
        
        End If
        
    End With
End Sub

Private Sub CommandButton1_Click()
   Dim i 'to store the item of the list
   Dim j 'just a counter
   Dim sht As Worksheet
    Set sht = Sheets("New Profile Part Template")
    j = 0 'Initiate the counter
   For Each i In Me.Results.List
    j = j + 1 'add one to the counter
    sht.Cells(9, 10).Value = sht.Cells(9, 10).Value & Chr(10) & i

    Next i
End Sub


Private Sub UserForm_Initialize()

    FormEvents = True

End Sub

The CommandButton1 near the end of the code is causing me problems; the Userform and multicolumn listbox function flawlessly. Only column 2 of the ListBox, titled "Results," needs to be extracted to cell J9. Preferably, the numbers should be separated by a space or comma.

Apr 9, 2023 in Others by Kithuzzz
• 38,000 points
669 views

1 answer to this question.

0 votes

Try this:

Private Sub CommandButton1_Click()

    Dim sht As Worksheet
    Set sht = Sheets("New Profile Part Template")

    Dim data As Variant
    data = Application.Index(Me.Results.List, 0, 2)
    
    sht.Cells(9, 10).Value = Application.TextJoin(", ", True, data)
    
End Sub
answered Apr 9, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,600 points
2,082 views
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,600 points
2,368 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
2,360 views
0 votes
1 answer

Export ListBox Data to Sheet range

Use ListCount to retrieve the number of ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,600 points
1,573 views
0 votes
1 answer

Excel VBA userform paste text in expanded format (not just to one cell)

It should work if you simply supply ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,600 points
651 views
0 votes
1 answer

EXCEL Userform - Creating multiple Labels and Textboxes with specific names

Please, test the next scenario: Insert a class ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,600 points
1,237 views
0 votes
1 answer

VBA - Msgbox inside a Loop

 Try this: Private Sub CommandButton1_Click() ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,000 points
728 views
0 votes
1 answer
0 votes
1 answer

How to split only one column to multiple rows while copying rest of the columns along the way?

Try this: =DROP(REDUCE(0,REDUCE(0,C2:C3,LAMBDA(a,x,VSTACK(a,CONCAT ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,600 points
445 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP