vba 通过vba excel按包含字符串的升序数字顺序对范围进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15110831/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sorting range in ascending numerical order containing strings by vba excel
提问by fabio.geraci
i have a range containing the following strings:
我有一个包含以下字符串的范围：
step_1, step_10, step_3, step_2
step_1、step_10、step_3、step_2
using the following code
使用以下代码
input_sh.Activate
With ActiveSheet
.Range("H2:H20").Select
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("H2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers 'xlSortNormal
With .Sort
.SetRange Range("H2:H20")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
step_10, step_1, step_2, step_3
step_10、step_1、step_2、step_3
but i would like to get
但我想得到
step_1, step_2,step_3,step_10
step_1、step_2、step_3、step_10
采纳答案by glh
I would separate the number into another column, your last + 1, using mid function and sort on that.
我会将数字分成另一列，即您的最后一个 + 1，使用 mid 函数并对其进行排序。
Edit: I'm not at my pc but I think your only way to do this is to setup a macro that:
编辑：我不在我的电脑前，但我认为你唯一的方法是设置一个宏：
- Filter your sheet by the first 9.
- Cut and insert them before row 2.
- Sort these on their own.
- Then remove the filter and sort the rest as you have above.
- 按前 9 个过滤您的工作表。
- 在第 2 行之前剪切并插入它们。
- 自己对这些进行排序。
- 然后取下过滤器并按照上面的方式对其余部分进行排序。
回答by glh
Your strings have underscore followed by numbers. if that is going to be format of your string you can simply split your string using convert text to columns using "_" as your delimiter. Later you can sort and concatenate to get your sorted list of strings.
您的字符串有下划线，后跟数字。如果这将是您的字符串格式，您可以简单地使用“_”作为分隔符将文本转换为列来拆分字符串。稍后您可以排序和连接以获得排序的字符串列表。
Sub Sample()
Columns(1).Copy Columns(3)
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Columns("D:D").Sort Range("D1")
i = 1
Do While Not IsEmpty(Range("C" & i))
Range("B" & i) = Range("C" & i) & "_" & Range("D" & i)
i = i + 1
Loop
End Sub
回答by fabio.geraci
thanks every one for you contribution. to user I found my solution before reading your suggestion. thanks anyway for your effort
感谢每一位的贡献。对于用户，我在阅读您的建议之前找到了我的解决方案。无论如何感谢你的努力
my solution:
我的解决方案：
- split str for "_"
- write 2nd index next to filenames order 2nd cols by then col with only number
- clean col with numbers
- 拆分 str 为“_”
- 在文件名旁边写入第二个索引，然后将第二列排序为只有数字的列
- 用数字清洁 col
