How can I sort a a table with VBA with given text condition?

Question

I am using a software that generates similar outputs in the picture below:

I would like to sort this table according to the numbers after the RetValue.

Is there an easy way to do this with VBA?

score 2 · Answer 1 · answered May 17 '16 at 11:49

2

I wouldn't go for VBA with this, but instead Split Text Into Multiple Cells and in Filter/Sort I would sort by the newly created "__Y" column.

answered May 17 '16 at 11:49

score 2 · Answer 2 · answered May 17 '16 at 11:58

2

With data in A1 through B4, in C1 enter:

=--LEFT(MID(A1,FIND(" ",A1)+1,9999),LEN(MID(A1,FIND(" ",A1)+1,9999))-1)

and then run the Recorded macro:

Sub Macro1()
    Range("A1:C4").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1:C4"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C4")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

answered May 17 '16 at 11:58

Gary's Student

95,722
10
59
99

Why do you post "new" sorting code when you and I just prepared such great sorting code including UDFs here: http://stackoverflow.com/questions/37239107/sort-range-without-sorting-it-in-a-spreadsheet/37239386#37239386 – Ralph May 17 '16 at 12:04
@Ralph In this particular case I wanted to emphasize the use of Recorded code along with the "helper" column.................but your approach is clearly the best answer! – Gary's Student May 17 '16 at 12:09
Of course, you are right. The macro recorder is an often forgotten and not enough used helper for most basic VBA code *(+1)* – Ralph May 17 '16 at 12:16

score 2 · Accepted Answer · edited May 23 '17 at 10:28

I am always a big fan of storing numbers in cells. Especially in Excel number are worth storing as such in order to allow for calculations. So, why don't you put into a cell just the number 36 (for example) with the

.NumberFormat = """RetValue ""00""Y"""

Then you still get to see RetValue 36Y but in fact only 36 is the .Value of the cell and you are allowed for calculations.

Still, to sort your above table there is nothing special to be considered. Since all cells start with the same string, all cells will be sorted anyway correctly. The only "problem" will be the RetValue 4Y since it is not a two-digit number.

Once again, I'd try to format it correctly. If that is not an option then I'd go for any of the solutions provided here to extract the number and then sort it as such. During this process I would (as described above) only store the number in the cell.Value and wrap the text around it as .NumberFormat to allow for more calculations in the future (if necessary).

Afterwards, you can sort the table for example with this solution: Sort range without sorting it in a spreadsheet

It gives you the option to sort it as an array in memory before pasting the result to the sheet or to use a UDF (entered as an array formula).

_{Note for the future: please don't post pictures but rather post formatted text we can copy for testing.}

score 0 · Answer 4 · answered May 17 '16 at 11:55

VBA may not be required for this.

You can insert the following formula in column C (assuming RetValue in Column A and Value in Column B and your data starts from 3rd row).

=MID(A3, FIND(" ",A3,1)+1, FIND("Y",A3,FIND(" ",A3,1))-10)

You can sort on column C.

How can I sort a a table with VBA with given text condition?

4 Answers4