VBA: How to auto sort a series in a single cell

I am looking for a way to automatically sort a series of comma-separated numbers in a single cell. I am using MS Excel.

In the attached spreadsheet I'm wanting to sort the numbers in Sheet 2, cells B2 and C2 into ascending numerical order.

Screenshot 2019-10-25 at 15.25.37.png

Thanks in advance.

chrubble

Yes, please. The spreadsheet that I attached to the post already has some VBA in it, also.

Thank you!

According to your attachment as a beginner starter :

PHP Code: 

Sub Demo1()
      Const S = ","
        Dim V, C%, W, N&
    With [B2:C2]
            V = .Value2
        For C = 1 To .Count
            With New Collection
                For Each W In Split(V(1, C), S)
                         W = Val(W)
                     For N = 1 To .Count
                        If .Item(N) > W Then .Add W, , N: N = 0: Exit For
                     Next
                        If N Then .Add W
                Next
                    V(1, C) = .Item(1)
                    For N = 2 To .Count:  V(1, C) = V(1, C) & S & .Item(N):  Next
            End With
        Next
           .Value2 = V
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

According to your attachment a starter demonstration via Excel basics formula :

PHP Code: 

Sub Demo2()
  Const S = ","
    Dim V, C&
  With [B2.C2]
        V = .Value2
    For C = 1 To .Count
        V(1, C) = Join(Evaluate("IF({1},SMALL({" & V(1, C) & "},COLUMN(" & [A1].Resize(, UBound(Split(V(1, C), S)) + 1).Address & ")))"), S)
    Next
       .Value2 = V
  End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Here is one more method that you can consider...

Please Login or Register  to view this content.

Originally Posted by Marc L

Please Login or Register  to view this content.

For Marc (or anyone else who might know)...

I have been using Evaluate for years now and I don't think I have come across, or at least noticed, this before. I was trying to simplify the above line of code and kept erroring out, so I decided to strip it down to the bare essentials with the idea of building it up from there. That is when I got stumped. In its barest form, your formula becomes this...

Please Login or Register  to view this content.

The resulting text string argument fed to the Evaluate function is identical to the resulting text string argument that your code feeds to the Evaluate function; however, your code runs and the one I wrote generates a "Type Mismatch" error? Just to reiterate, when I put a breakpoint on that line of code and execute these two statements (the text string argument for the Evaluate function)...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

they both print out the identical text string. So my question... why does the Evaluate function care how its text string argument is constructed?

Originally Posted by Rick Rothstein

Here is one more method that you can consider...

Nope ‼ chrubble can't consider your Windows ArrayList on his MAC version ! The reason why I started with a VBA Collection …

Originally Posted by Rick Rothstein

they both print out the identical text string. So my question... why does the Evaluate function care how its text string argument is constructed?

Nope again as the Evaluate method does not care about the string construction but just about its value !
So your bad as you forgot to check out for the second cell, C2 !
Just using Debug.Print you will see your formula is not the same as mine …

A formula variation :

PHP Code: 

Sub Demo2a()
  Const F = "IF({1},SMALL({¤},COLUMN(A:#)))", S = ","
    Dim V, C&
  With [B2.C2]
        V = .Value2
    For C = 1 To .Count
        V(1, C) = Join(Evaluate(Replace (Replace(F, "¤", V(1, C)), "#", Chr(UBound(Split(V(1, C), S)) + 65))), S)
    Next
       .Value2 = V
  End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Hi Marc L - Thanks for the response . How do I add this into VBA? As a new module? Sorry, I'm a complete novice with VBA. Currently my VBA project looks like this...
Screenshot 2019-10-28 at 10.44.46.png
All the best,
chrubble

Directly in the worksheet module …

Hi Mark L,

Thanks for your helpful comment.

Can you explain to me how to add your PHP code "directly in the worksheet module"?

Many thanks,

chrubble

Originally Posted by rorya

I'd suggest you insert a new module (or use the existing one) and just copy and paste it in.

Thanks Rory.

The structure of my VBA Project is posted above - Which directory or folder should I create the module in? The 'Modules' sub-directory of the 'VBAProject (Sample_1294080_Mac.xlsb)' project?

Many thanks,

chrubble

Originally Posted by chrubble

Can you explain to me how to add your PHP code "directly in the worksheet module"?

Two ways to paste my VBA code :

• On VBE side double click on the worksheet name in the Project window of your workbook
  
  
• Right click on the worksheet tab name then choose the code option …

Thanks Rory and Mark,

I added the code as a new module (Module 2) under the 'VBAProject (Sample_1294080_Mac.xlsb)' project. But when I try to run it, I get a run-time '424' (Object required) error. The debug shows that the issue is the 'V = .Value2' line in the code.

Attachment 648414

Any suggestions?

I've attached the updated version of the file.

Many thanks,

chrubble

Perfect - That worked. THANK YOU!