- Sub qcw()
- Application.ScreenUpdating = False
- Dim brr(), i, j, m, n, r, F, W
- F = False
- For Each W In Workbooks
- If W.Name = "数据表.xlsm" Then
- F = True
- MsgBox "数据表工作薄已经打开"
- Exit Sub
- End If
- Next W
- If F = False Then
- Set wb = Workbooks.Open("" & ThisWorkbook.Path & "\数据表.xlsm")
- arr = wb.Sheets(1).Range("a2:g" & wb.Sheets(1).[Match(1, 0 / (A:A <> ""))])
- wb.Close
- r = Range("b65536").End(xlUp).Row
- ReDim brr(1 To r, 1 To 2)
- For i = 4 To r - 1
- n = 0: m = 0
- For j = 1 To UBound(arr)
- If arr(j, 4) = Range("c2") And arr(j, 5) = Cells(i, 2) Then '
- n = arr(j, 7) + n: Y = arr(j, 7) + Y
- End If
- If arr(j, 3) = Range("d2") And arr(j, 5) = Cells(i, 2) Then '
- m = arr(j, 7) + m: Z = arr(j, 7) + Z
- End If
- Next j
- brr(i - 3, 1) = n: brr(i - 3, 2) = m
- Next i
- brr(r - 3, 1) = Y: brr(r - 3, 2) = Z
- [c4].Resize(r, 2) = brr
- End If
- Application.ScreenUpdating = True
- End Sub
复制代码 |