|
Sub test()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
Set sh = ThisWorkbook.Worksheets(1)
If sh.[a1] = "销量" Then
y = 3
ElseIf sh.[a1] = "销售金额" Then
y = 7
ElseIf sh.[a1] = "销售量" Then
y = 8
End If
sh.Range("b3:ab6") = Empty
ar = sh.Range("a1:ab6")
For j = 2 To UBound(ar, 2)
If Trim(ar(1, j)) = "" Then ar(1, j) = ar(1, j - 1)
Next j
For j = 2 To UBound(ar, 2)
If Trim(ar(1, j)) <> "" And Trim(ar(2, j)) <> "" Then
d(Trim(ar(1, j)) & "|" & Trim(ar(2, j))) = j
End If
Next j
For i = 3 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
d(Trim(ar(i, 1))) = i
End If
Next i
f = Dir(ThisWorkbook.Path & "\各省数据.XLSX")
If f <> "" Then
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f, 0)
For Each sht In wb.Worksheets
br = sht.UsedRange
For i = 2 To UBound(br)
m = d(Trim(br(i, 1)))
n = d(sht.Name & "|" & Trim(br(i, 2)))
If n <> "" And m <> "" Then
ar(m, n) = ar(m, n) + br(i, y)
End If
Next i
Next sht
wb.Close False
End If
sh.Range("a1:ab6") = ar
MsgBox "ok!"
End Sub
|
|