|
发表于 2012-11-21 10:14
|
显示全部楼层
本楼为最佳答案
是因为有数据的列超过了256了。
如果你有07版本的,可用以下代码:- Sub lqxs()
- Dim Arr, i&, x$, x1$, d1, d2, j&, aa, Brr, ii&, m&
- Dim d, k, t, d3, t3
- Dim Sht As Worksheet, Sht1 As Worksheet
- Application.DisplayAlerts = False
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- Set d3 = CreateObject("Scripting.Dictionary")
- Sheet1.Activate
- For Each Sht In Sheets
- If Sht.Name <> "数据库" Then Sht.Delete
- Next Sht
- Call px
- Arr = Sheet1.[a1].CurrentRegion
- For i = 2 To UBound(Arr) - 1
- x1 = Arr(i, 1) & "-" & Arr(i, 2) & "|" & Arr(i, 4) & "," & Arr(i, 6) & "," & Arr(i, 7)
- d1(x1) = d1(x1) + Arr(i, 8) '数量
- x = Arr(i, 4)
- If InStr(d2(x), Arr(i, 1) & "-" & Arr(i, 2)) = 0 Then
- d2(x) = d2(x) & Arr(i, 1) & "-" & Arr(i, 2) & "|" '日期
- End If
- If InStr(d(x), Arr(i, 6) & "," & Arr(i, 7)) = 0 Then
- d(x) = d(x) & Arr(i, 6) & "," & Arr(i, 7) & "|" '商品名称
- End If
- d3(x) = Arr(i, 5) '客户
- Next
- k = d.keys
- t = d.items: t3 = d3.items
- For i = 0 To UBound(k)
- t(i) = Left(t(i), Len(t(i)) - 1)
- Sheets.Add after:=Sheets(Sheets.Count)
- Set Sht1 = ActiveSheet
- Sht1.Cells.Font.Size = 10
- Sht1.Name = t3(i)
- Sht1.[a1] = "客户:" & t3(i)
- Sht1.[a3] = "商品代码": Sht1.[b3] = "商品名称"
- If InStr(t(i), "|") Then
- aa = Split(t(i), "|")
- For j = 0 To UBound(aa)
- Sht1.Cells(j + 4, 1) = aa(j)
- Next
- Sht1.Cells(j + 4, 1) = "总计": m = j + 4
- Else
- End If
- t2 = d2(k(i))
- t2 = Left(t2, Len(t2) - 1)
- If InStr(t2, "|") Then
- aa = Split(t2, "|")
- For j = 0 To UBound(aa)
- Sht1.Cells(3, j + 3) = aa(j)
- Next
- End If
- Brr = Sht1.[a3].CurrentRegion
- For ii = 2 To UBound(Brr) - 1
- For j = 3 To UBound(Brr, 2)
- x1 = Brr(1, j) & "|" & k(i) & "," & Brr(ii, 1)
- If d1.exists(x1) Then Brr(ii, j) = d1(x1)
- Next
- Next
- Sht1.[a3].CurrentRegion = Brr
- Sht1.[a4].Resize(UBound(aa) + 1).TextToColumns Comma:=True
- Sht1.[a3].CurrentRegion.Borders.LineStyle = 1
- Sht1.Cells(m, 3).Formula = "=sum(r4c:r[-1]c)"
- Sht1.Cells(m, 3).AutoFill Sht1.Cells(m, 3).Resize(1, UBound(Brr, 2) - 2)
- Next
- Application.DisplayAlerts = True
- End Sub
- Sub px()
- Dim Myr&
- Sheet1.Activate
- Myr = [a65536].End(xlUp).Row
- Range("A2").Select
- ActiveWorkbook.Worksheets("数据库").AutoFilter.Sort.SortFields.Clear
- ActiveWorkbook.Worksheets("数据库").AutoFilter.Sort.SortFields.Add Key:=Range( _
- "A2:A" & Myr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
- xlSortTextAsNumbers
- ActiveWorkbook.Worksheets("数据库").AutoFilter.Sort.SortFields.Add Key:=Range( _
- "B2:B" & Myr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
- xlSortTextAsNumbers
- With ActiveWorkbook.Worksheets("数据库").AutoFilter.Sort
- .Header = xlYes
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
- End Sub
复制代码 |
|