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

[chrubble]

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

[AliGW]

Can't be done without VBA - shall I move the thread for you?

[chrubble]

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

Thank you!

[Marc L]

According to your attachment as a beginner starter :

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 » !

[Marc L]

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

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 » !

[Rick Rothstein]

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

Please Login or Register  to view this content.

[Rick Rothstein]

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?

[Marc L]

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 …

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 …

[Marc L]

A formula variation :

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 » !

[chrubble]

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

[Marc L]

Directly in the worksheet module …

[AliGW]

Marc L - the OP has said they are a novice. Please explain HOW to get something "directly in the worksheet module". Thanks.

[chrubble]

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

[rorya]

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

[chrubble]

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

[Marc L]

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 …

[rorya]

Yes, or you could simply add it to the existing Module1.

[chrubble]

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

[rorya]

Try this version:

Please Login or Register  to view this content.

[chrubble]

Perfect - That worked. THANK YOU!