|
先排序,再输出。- Sub 比较()
- [B1:IV65536].ClearContents
- Dim fd As FileDialog, wb As Workbook, sh As Worksheet, Mypath As String, arrf$(), mf&
- With Application.FileDialog(msoFileDialogFolderPicker)
- .AllowMultiSelect = False
- If .Show = -1 Then
- Mypath = .SelectedItems(1)
- If Right(Mypath, 1) <> "" Then Mypath = Mypath & ""
- Else
- If MsgBox("以本工作簿所在路径为默认文件夹吗?" _
- & vbNewLine & vbNewLine & "单击“否”可退出程序", vbYesNo, "确认默认路径") = vbYes Then
- Mypath = ThisWorkbook.Path
- If Right(Mypath, 1) <> "" Then Mypath = Mypath & ""
- Else
- GoTo The_Exit
- End If
- End If
- End With
- Application.ScreenUpdating = False
- If Mypath <> "" Then '如果选到
-
- Call GetFiles(Mypath, arrf, mf)
- Set d = CreateObject("scripting.dictionary") 'arrf内排序
- For k = 1 To UBound(arrf) '读取月份,存入字典
- F = arrf(k)
- If InStr(F, ThisWorkbook.Name) = 0 Then
- yf = Val(Split(F, "")(UBound(Split(F, ""))))
- d(F) = yf
- End If
- Next
- For k = 1 To UBound(arrf) - 1 '双循环比较,排序
- For k1 = k + 1 To UBound(arrf)
- F = arrf(k): F1 = arrf(k1)
- If InStr(F, ThisWorkbook.Name) = 0 And InStr(F1, ThisWorkbook.Name) = 0 Then
- If d(F) > d(F1) Then
- tmp = arrf(k): arrf(k) = arrf(k1): arrf(k1) = tmp
- End If
- End If
- Next
- Next
-
- c = 1
- xrr = Sheets("get").Range("A1:A" & Sheets("get").[A65536].End(3).Row) '当前工作A列数据
- For Each F In arrf '打开所有文件(已排序)
- If InStr(F, ThisWorkbook.Name) = 0 Then
- Set wb = Workbooks.Open(F)
- For Each sh In wb.Worksheets
- If Application.WorksheetFunction.CountA(sh.UsedRange) Then
- xname = Split(wb.Name, ".")(0) '工作表名
- Set d = CreateObject("scripting.dictionary")
- 'arr = sh.[a1].CurrentRegion
- ' arr = sh.UsedRange
- Set rng1 = sh.UsedRange.Find("车间")
- Set Rng2 = sh.UsedRange.Find("产量")
- If Not rng1 Is Nothing And Not Rng2 Is Nothing Then
- c1 = rng1.Column
- c2 = Rng2.Column
- r = sh.Cells(Rows.Count, c2).End(3).Row '产量列的最大行
- arr = sh.[A1].Resize(r, Application.Max(c1, c2)) '定义数组!
- For i = rng1.Row + 1 To UBound(arr) '打开工作表A列和B列相关联
- d(arr(i, 1)) = arr(i, c2)
- Next
- End If
- End If
- Next
- wb.Close False
- ReDim yrr(1 To UBound(xrr), 1 To 1): yrr(1, 1) = xname
- For i = 2 To UBound(xrr)
- yrr(i, 1) = d(xrr(i, 1))
- Next
- c = c + 1
- ' c = getNum(xname) + 1 ''''''''''''''''''''''''''根据月份得到列号
- ' maxc = IIf(c > maxc, c, maxc)
- Sheets("get").Cells(1, c).Resize(UBound(yrr), 1) = yrr
- d.RemoveAll
- End If
- Next
- End If
- ' With Sheets("get") '判断空列并删除
- ' For j = maxc To 2 Step -1
- ' If Application.WorksheetFunction.CountA(.Columns(j)) = 0 Then .Columns(j).Delete
- ' Next
- ' End With
- The_Exit:
- Application.ScreenUpdating = True
- End Sub
复制代码 |
评分
-
查看全部评分
|