Excel Macro to replace all cell commas with hard returns 1

[s0crates9]

Let's say that I have an excel sheet with words seperated by commas, and I want to create new lines/hard returns - how would I create a macro for that. So Far I was able to find a method to do this to the first comma on a cell, but not how to loop through all the commas in a cell and not the first one.

Here is what I am usinfg in my macro to accomplish this:

Public Sub Create_List()
    For Each Cell In Selection
        'Cell.Value = Replace(Cell.Value, ",", Chr(10), 1, 1, vbTextCompare)
        Cell.Value = Replace(Cell.Value, ",", Chr(10), 1, 1, vbTextCompare)
    Next Cell
End Sub

any one know what will change this? I am not a macro guy by any stretch, but I would like this functionality and recording the macro doesn't accomplish what I want either.

Web site design, internet marketing, SEO and business solutions company.

http://www.startegy.net

http://www.dominus-web.com

http://websitedesigncompany.blogspot.com/

 

[PHV]

Why not simply this ?
Cell.Value = Replace(Cell.Value, ",", Chr(10))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[s0crates9]

Brilliant.

Thanks so much, exactly what I needed. I knew it was overworked on my behalf!

Thanks PHV

Web site design, internet marketing, SEO and business solutions company.

http://www.startegy.net

http://www.dominus-web.com

http://websitedesigncompany.blogspot.com/