Excel VBA, trim

Hi guys,

basically what i have is a cell with a couple of price codes all consist of 4 characters. I am able to pull them from the cell and populate a listbox with one item, however the cell has a number of items in it which i would like to be listed seperatly (as their own item in the list)

example codes
PS7D, PSHD, NW7D

I would like them to list as
PS7D
PDHD
NW7D
(multiple items)

Not
PS7D, PSHD, NW7D
(one item)

How can i achieve this as at the moments they are all listing as one item in the listbox

here is the code i am using to pull the information from the cell, if it helps

Code:

---

'This clears the listbox on the frmReturns form so that new values can be added
frmReturns.lstRentals.Clear

Dim ws As Worksheet
Dim ilastrow As Long
Dim irow As Long
 
Set ws = Worksheets("CustomerDetails")
ilastrow = lstCustomers.ListIndex + 5
 
For irow = lstCustomers.ListIndex + 5 To ilastrow 'u cannot start from 0 bcoz there is no such cell-A0
    If Trim(ws.Range("H" & irow).Value) <> "" Then
        With frmReturns.lstRentals
            .AddItem Trim(ws.Range("H" & irow).Value)
        End With
    End If
Next irow

---

Thanks,

Marc

Have a look at the Split method. It will split the string into an array, and then you can loop through that array to add single items.

-Terry

Something like

Code:

---

Private Sub CommandButton1_Click()
Dim sTest As String
Dim arrTest() As String
Dim i As Long
sTest = "PS7D, PSHD, NW7D"
arrTest = Split(sTest, ",")
For i = 0 To UBound(arrTest)
    ListBox1.AddItem Trim(arrTest(i))
Next
End Sub

---