excel vba排序表数字 [英] excel vba sort sheet numerically
本文介绍了excel vba排序表数字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
############################## #UPDATE1
我修改了如下代码。但是它按降序排序:(我想按升序排列
Option Explicit
Sub SortWorksheets()
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
SortDescending = False
如果ActiveWindow.SelectedSheets.Count = 1然后
'将1更改为要先排序的工作表
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
使用ActiveWindow.SelectedSheets
对于N = 2 To .Count
如果.Item(N - 1).Index <> .Item(N).Index - 1然后
MsgBox您不能排序不相邻的工作表
退出子
结束如果
下一个N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Inde x
结束
结束如果
对于M = FirstWSToSort到LastWSToSort
对于N = M到LastWSToSort
如果SortDescending = True然后
如果CLng(Split(Worksheets(N).Name,_)(0))> _
CLng(Split(Worksheets(M).Name,_)(0))然后
工作表(N).Move Before:= Worksheets(M)
End If
Else
如果CLng(Split(Worksheets(N).Name,_)(0))> _
CLng(Split(Worksheets(M).Name,_)(0))然后
工作表(N).Move Before:= Worksheets(M)
End If
结束如果
下一个N
下一个M
End Sub
解决方案
而不是这样:
如果UCase(Worksheets(N) .Name)> UCase(Worksheets(M).Name)然后
你需要这样的东西:
如果CLng(Split(Worksheets(N).Name,_)(0))> _
CLng(Split(Worksheets(M).Name,_)(0))然后
I want to sort sheets in my excel file. I found this code which works. But the problem is that my sheet names are 1_abc, 2_adf, 3_dasf, 11_ad etc. This code puts 11_ad before 2_adf and 3_dasf. I would like to sort sheets based upon number before "_" (first underscore) in my sheet name. How could I do the same?
################################UPDATE1I modified the code as below. But it is sorting in descending order :(. I want to sort in ascending order
Option Explicit
Sub SortWorksheets()
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
SortDescending = False
If ActiveWindow.SelectedSheets.Count = 1 Then
'Change the 1 to the worksheet you want sorted first
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index <> .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If
For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If CLng(Split(Worksheets(N).Name, "_")(0)) > _
CLng(Split(Worksheets(M).Name, "_")(0)) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If CLng(Split(Worksheets(N).Name, "_")(0)) > _
CLng(Split(Worksheets(M).Name, "_")(0)) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M
End Sub
解决方案
Instead of this:
If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
you need something like this:
If CLng(Split(Worksheets(N).Name,"_")(0)) > _
CLng(Split(Worksheets(M).Name,"_")(0)) Then
这篇关于excel vba排序表数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文