Excel VBA, trim

[Marc6678]

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

'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

[TerryTee]

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

[Hack]

Something like

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