Convert multiple text lines in 1 cell to an unordered list

[rajsidhu]

I read a post in this forum that shows how to make multiple text lines, in 1 cell, into bullet points which works great. Here is the code:-

Sub aTest()
    Dim rCell As Range, spl As Variant, i As Long
    
    For Each rCell In Selection
        spl = Split(rCell, Chr(10))
        For i = LBound(spl) To UBound(spl)
            spl(i) = Chr(60) & "li" & Chr(62) & spl(i) & Chr(60) & "/li" & Chr(62)
        Next i
        rCell = Join(spl, Chr(10))
    Next rCell
    Selection.ColumnWidth = 200
    Selection.EntireRow.AutoFit
    Selection.EntireColumn.AutoFit
End Sub

What i need to do is create an unordered list out of these bullet points.

Is there any one who can help me modify the above code so that it spits out an unordered list of bullet points please?

 

[rajsidhu]

What exactly is "unordered" about the desired result you show in post #4?
What is the error message and what line is highlighted when you get the error you refer to in post #8?

Hi JoeMo,

So your code worked as so far it made each line in the cell a list item which is what i want. But i also need the code to add the outer tags <ul> and </ul>

The error I get when updating the code as suggested by Fluff is -

Can't execute code in break mode

and the line i add is highlighted yellow.

 

[rajsidhu]

It's in the code you originally posted.

Excellent. I got it to work.

I was adding the line to the code given by JoMoe and didn't realise i should have added it to my original code.

Thank you guys so much

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JoeMo]

For the record, the code below will produce the outer tags and return the bullets in random order. And I still wonder what the OP means by "unordered" ????????

Sub aTest()
    Dim rCell As Range, splIN As Variant, splOUT As Variant, i As Long, j As Long, d As Object
    Set d = CreateObject("Scripting.dictionary")
    For Each rCell In Selection
        d.RemoveAll
        splIN = Split(rCell, Chr(10))
        ReDim splOUT(LBound(splIN) To UBound(splIN))
        For i = LBound(splIN) To UBound(splIN)
Again:      j = WorksheetFunction.RandBetween(LBound(splIN), UBound(splIN))
            If Not d.exists(j) Then
                d.Add j, d.Count
                splOUT(j) = Chr(60) & "li" & Chr(62) & splIN(i) & Chr(60) & "/li" & Chr(62)
            ElseIf d.Count < UBound(splIN) + 1 Then
                GoTo Again
            End If
        Next i
        rCell = "<ul>" & Chr(10) & Join(splOUT, Chr(10)) & Chr(10) & "</ul>"     'Join(splOUT, Chr(10))
    Next rCell
Selection.ColumnWidth = 200
Selection.EntireRow.AutoFit
Selection.EntireColumn.AutoFit
End Sub

 

[Fluff]

And I still wonder what the OP means by "unordered"

That makes two of us.

 

[rajsidhu]

Here's a short video explaining what unordered and ordered lists are.

HTML Ordered and Unordered Lists

The reason i wanted to add the outer ul tags was because when you import the list this way into a WooCommerce page, it left aligns the list better.

Anyway, thanks for helping me with this, although I'm probably going to have ask more questions in the near future!

If anyone could recommend a simple to follow course on VBA for someone who doesn't code, that would really help?