collection values to a single cell

[skacutter]

I have a collection in VBA of file paths. I need to record each value in the collection to a single cell with each value on a new line in the cell. Possible????

existing code

Dim colFiles As New Collection

If ListBox3.ListCount > -1 Then


For i = 0 To ListBox3.ListCount - 1
UserChanges = UserChanges & vbCrLf & ListBox3.List(i) & "      " & ListBox3.List(i, 1) & "     " & ListBox3.List(i, 2) & "     " & ListBox3.List(i, 3)
Attachments1 = ListBox3.List(i, 3)
colFiles.Add Attachments1


Next i


End If

Followed by

If colFiles.Count > 0 Then            For i = 1 To colFiles.Count
                Cells(Rev_Row, "S").Value.???Add???? colFiles(i)
            Next i
        End If

Thank You
Chris

 

[Coding4Fun]

I m not fully understanding what you are doing with the code you provided but I think this might help you.
When I tried to copy and run this I got n error it did not want to process this line but I don't see where UserChanges is called out

UserChanges = UserChanges & vbCrLf & ListBox3.List(i) & "      " & ListBox3.List(i, 1) & "     " & ListBox3.List(i, 2) & "     " & ListBox3.List(i, 3)
Attachments1 = ListBox3.List(i, 3)
colFiles.Add Attachments1

This will take all the values in your listbox and display them in a single cell, each item in your listbox will be displayed on a new line. From your code it looks like you have a listbox with 4 columns.

Might be enough to get you in the right direction.

Sub Tester2()
    OnRow = 1
    'If colFiles.Count > 0 Then
    For e = 0 To ListBox3.ListCount - 1
                
                Dim Var1 As String
                Var1 = ListBox3.List(e, 0) & ListBox3.List(e, 1) & ListBox3.List(e, 2) & ListBox3.List(e, 3)
                Var2 = Range("A1").Value
                Range("A1").Value = Var1
                Range("A2").Value = Var1 & vbNewLine & Var2
                OnRow = OnRow + 1
                Next e
      
End Sub

 

[skacutter]

I m not fully understanding what you are doing with the code you provided but I think this might help you.
When I tried to copy and run this I got n error it did not want to process this line but I don't see where UserChanges is called out

UserChanges = UserChanges & vbCrLf & ListBox3.List(i) & "      " & ListBox3.List(i, 1) & "     " & ListBox3.List(i, 2) & "     " & ListBox3.List(i, 3)
Attachments1 = ListBox3.List(i, 3)
colFiles.Add Attachments1

This will take all the values in your listbox and display them in a single cell, each item in your listbox will be displayed on a new line. From your code it looks like you have a listbox with 4 columns.

Might be enough to get you in the right direction.

Sub Tester2()
    OnRow = 1
    'If colFiles.Count > 0 Then
    For e = 0 To ListBox3.ListCount - 1
                
                Dim Var1 As String
                Var1 = ListBox3.List(e, 0) & ListBox3.List(e, 1) & ListBox3.List(e, 2) & ListBox3.List(e, 3)
                Var2 = Range("A1").Value
                Range("A1").Value = Var1
                Range("A2").Value = Var1 & vbNewLine & Var2
                OnRow = OnRow + 1
                Next e
      
End Sub

This worked very well. You hard coded the target cells and all I changed was to make the row dynamic and reuse the code for each column in the listbox-3. Now for each row in the listbox I can record multiple row values to a single row in excel separated by columns

Thank You.

Chris