|
楼主 |
发表于 2020-2-22 16:20
|
显示全部楼层
Sub 按钮1_Click()
Dim crr()
Set d = CreateObject("scripting.dictionary")
Set dd = CreateObject("scripting.dictionary")
Set fso = CreateObject("scripting.filesystemobject")
Application.ScreenUpdating = False
For Each f In fso.getfolder(ThisWorkbook.Path).Files
If InStr(f.Name, "每日报表") = 0 Then
With Workbooks.Open(f)
arr = .Sheets(1).UsedRange
.Close False
End With
d.RemoveAll
dd.RemoveAll
For j = 2 To UBound(arr)
If Len(arr(j, 2)) > 0 Then
str1 = Left(arr(j, 7), 2) & "@#$@" & arr(j, 2) & "@#$@" & arr(j, 3) & "@#$@" & arr(j, 4)
d(str1) = d(str1) + arr(j, 5)
dd(str1) = dd(str1) + arr(j, 6)
End If
Next j
For Each sht In ThisWorkbook.Sheets
brr = Split(sht.Name, "、")
ReDim crr(1 To d.Count, 1 To 5)
r = 0
For x = 0 To d.Count - 1
str1 = d.keys()(x)
For Z = 0 To UBound(brr)
If Left(str1, 2) = brr(Z) Then
arr = Split(str1, "@#$@")
r = r + 1
crr(r, 1) = arr(1)
crr(r, 2) = arr(2)
crr(r, 3) = arr(3)
crr(r, 4) = d(str1)
crr(r, 5) = dd(str1)
End If
Next Z
Next x
For i = 1 To r
For j = i + 1 To r
If crr(j, 5) > crr(i, 5) Then
For x = 1 To 5
tmp = crr(i, x)
crr(i, x) = crr(j, x)
crr(j, x) = tmp
Next x
End If
Next j
Next i
If r > 0 Then
nm = Left(f.Name, 2)
If nm = "总店" Then
sht.[b4].Resize(WorksheetFunction.Min(r, 15), 5) = crr
Else
For x = 8 To sht.UsedRange.Columns.Count Step 6
ww = Left(sht.Cells(2, x), 2)
If ww = nm Then
sht.Cells(4, x).Resize(WorksheetFunction.Min(r, 15), 5) = crr
Exit For
End If
Next
End If
End If
Next sht
End If
Next f
Application.ScreenUpdating = True
End Sub
今天又加了两个类别,如果01、09 我要加15开头, 就是01、09、15 。05、11、12 要加上14 类别 ,改成05、11、12、14 请问代码改哪里 |
|