如何用给定的文本条件对具有VBA的表进行排序？ [英] How can I sort a a table with VBA with given text condition?

问题描述

我正在使用以下图片中产生类似输出的软件：
< img src =https://i.stack.imgur.com/kSZVP.pngalt =在此输入图像说明>

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

我会喜欢根据RetValue之后的数字对这个表进行排序。

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

有没有一个简单的方法来做这个VBA？

Is there an easy way to do this with VBA?

推荐答案

我一直都是在单元格中存储数字的大粉丝。特别是在Excel中，数值是值得存储的，以便进行计算。那么，为什么你不把这个数字放在36号（例如）的

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"""

然后你还会看到 RetValue 36Y 但实际上只有36是单元格的 .Value ，您可以进行计算。

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.

然而，为了排列你的上表，没有什么特别要考虑的。由于所有单元格都以相同的字符串开头，所以所有单元格都将被正确排序。唯一的问题将是 RetValue 4Y ，因为它不是两位数字。

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.

再次，我会尝试格式化它。如果这不是一个选择，那么我会去提供这里提供的任何解决方案提取数字，然后将其排序。在这个过程中，我将（如上所述）仅将数字存储在 cell.Value 中，并将文本围绕它作为 .NumberFormat 以便将来进行更多的计算（如有必要）。

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).

此后，您可以使用此解决方案对表进行排序：
< a href =https://stackoverflow.com/questions/37239107/sort-range-without-sorting-it-in-a-spreadsheet/37239386#37239386>排序范围，而不在电子表格中排序

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

它可以将粘贴到表格或使用UDF（作为数组公式输入）之前将其排序为内存中的数组。

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).

_{未来注意事项：请不要张贴图片，而是发布格式文本，我们可以复制以进行测试。}

这篇关于如何用给定的文本条件对具有VBA的表进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！