|
1学分
首先在E列添加公式(=IF(COUNTIF([标准件清单.xlsx]Sheet1!$A$1:$A$522,C3),"1","2")),对C列进行材料分类,后对E列跟C列进行自定义排序,E列优先,BCD3列对应的数据不能乱,一般都有150个左右的工作簿。
Sub 排序()
Dim fso As Object
Dim Wb As Workbook
Dim Sh As Worksheet
Dim D As Object
Dim R&, Ar, f, T
Application.DisplayAlerts = False
Application.ScreenUpdating = False
T = Timer
Set fso = CreateObject("Scripting.FileSystemObject")
Set D = CreateObject("scripting.dictionary")
With Sheet1
Ar = .[A1].CurrentRegion
For R = 1 To UBound(Ar)
D(Ar(R, 1)) = ""
Next R
End With
For Each f In fso.getfolder(ThisWorkbook.Path).Files
If InStr(f.Name, ThisWorkbook.Name) = 0 Then
With Workbooks.Open(f)
For Each Sh In .Worksheets
If Application.CountA(Sh.Cells) > 0 Then
With Sh
R = .Cells(.Rows.Count, 3).End(xlUp).Row
Ar = .Cells(3, 1).Resize(R - 2, 5)
For R = 1 To UBound(Ar)
If D.exists(Ar(R, 3)) Then
Ar(R, 5) = "1"
Else
Ar(R, 5) = "2"
End If
Next R
.Cells(3, 1).Resize(UBound(Ar), 5) = Ar
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("E3:E" & UBound(Ar) + 2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=.Range("C3:C" & UBound(Ar) + 2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("A3:E" & UBound(Ar) + 2)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Columns("E") = ""
End With
End If
Next Sh
.Save
.Close False
End With
End If
Next f
MsgBox "用时:" & Format(Timer - T, "0.00\秒")
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
|
最佳答案
查看完整内容
Sub 排序()
Dim fso As Object
Dim Wb As Workbook
Dim Sh As Worksheet
Dim D As Object
Dim R&, Ar, f, T
Application.DisplayAlerts = False
Application.ScreenUpdating = False
T = Timer
Set fso = CreateObject("Scripting.FileSystemObject")
Set D = CreateObject("scripting.dictionary")
With Sheet1
Ar = .[A1].CurrentRegion
For R = 1 To ...
|