如何做跨頁出貨單歷史統計

janejacky

1.如何在出貨單歷史統計表中顯示出貨單資料

2.出貨單  統計完後當日還可繼續輸入第2筆不同客戶的資料  也會繼續顯示在出貨單歷史統計表中

要如何才能做出來ㄋ

GBKEE

回復 1# janejacky

更正:
試試看

1. Sub Ex()
   
2.     Dim D(2) As Object, R As Variant, AR()
   
3.     Set D(0) = CreateObject("Scripting.Dictionary")
   
4.     Set D(1) = CreateObject("Scripting.Dictionary")
   
5.     Set D(2) = CreateObject("Scripting.Dictionary")
   
6.     With Sheets("出貨單")
   
7.         For Each R In .Range(.[B12], .[G29]).Rows   '出貨單內容範圍-> 的整列
   
8.             If Application.CountA(R) = 6 Then   '資料要齊全
   
9.                 AR = Array(.[G4].Text, .[G5], .[B5], R.Cells(1, 1), R.Cells(1, 2), R.Cells(1, 3), R.Cells(1, 5), R.Cells(1, 6))
   
10.                 D(1)(Join(AR, ",")) = AR
    
11.             End If
    
12.         Next
    
13.     End With
    
14.     With Sheets("出貨單歷史統計")
    
15.         For Each R In .Range(.[A3], .Cells(Rows.Count, "H").End(xlUp)).Rows
    
16.             If Application.CountA(R) = 8 Then D(0)(Join(Application.Transpose(Application.Transpose(R.Value)), ",")) = ""
    
17.             D(2)(R.Cells(1, 1) & R.Cells(1, 2)) = D(2)(R.Cells(1, 1) & R.Cells(1, 2)) + R.Cells(1, 8)
    
18.         Next
    
19.         For Each R In D(1).KEYS
    
20.             If D(0).EXISTS(R) = False Then
    
21.                 With .Cells(Rows.Count, "A").End(xlUp).Offset(1)
    
22.                     .Resize(, 8) = D(1)(R)
    
23.                     D(2)(.Cells(1) & .Cells(1, 2)) = D(2)(.Cells(1) & .Cells(1, 2)) + .Cells(1, 8)
    
24.                 End With
    
25.             End If
    
26.         Next
    
27.         For Each R In .Range(.[A3], .Cells(Rows.Count, "A").End(xlUp))
    
28.             If D(2).EXISTS(R & R(1, 2)) Then R(1, 9) = D(2)(R & R(1, 2))
    
29.         Next
    
30.     End With
    
31.     Set D(0) = Nothing
    
32.     Set D(1) = Nothing
    
33.     Set R = Nothing
    
34. End Sub

複製代碼

janejacky

我試過
但客戶名稱沒有到出貨統計表中ㄝ

Hsieh

回復 1# janejacky


    試試附件
出貨單歷史統計.rar (12.57 KB)

下載次數: 11

2011-5-9 00:03

1. Sub inputdata() '儲存資料
   
2. Dim Rng As Range, Ay()
   
3. With Sheet1
   
4. Set Rng = .Range("A12:A29")
   
5. If Application.CountA(Rng) > 0 Then
   
6.    For Each a In Rng.SpecialCells(xlCellTypeConstants)
   
7.       ar = Array(.[G5].Value, .[G4].Value, .[B5].Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value, a.Offset(, 5).Value, a.Offset(, 6).Value)
   
8.       ReDim Preserve Ay(s)
   
9.       Ay(s) = ar
   
10.       s = s + 1
    
11.    Next
    
12.    cnt = .[G32].Value
    
13.    With Sheet2
    
14.       Set a = .[A65536].End(xlUp).Offset(1)
    
15.       a.Resize(s, 8) = Application.Transpose(Application.Transpose(Ay))
    
16.       a.Offset(s - 1, 8) = cnt
    
17.    End With
    
18. End If
    
19. End With
    
20. End Sub
    
21. Function PaperNo(Rng As Range, mydate As Date, k) '流水編號
    
22. Set d = CreateObject("Scripting.Dictionary")
    
23. mystr = Format(mydate, "yyyymmdd")
    
24. If Application.CountA(Rng) > 0 Then
    
25. For Each a In Rng.SpecialCells(xlCellTypeConstants)
    
26.     If Left(a, 8) = mystr Then d(Val(a)) = ""
    
27. Next
    
28. End If
    
29. If d.Count > 0 Then
    
30. PaperNo = IIf(k = 1, Format(Application.Max(d.keys) + 1, "00000000000"), Format(Application.Max(d.keys), "00000000000"))
    
31. Else
    
32. PaperNo = mystr & "001"
    
33. End If
    
34. End Function

複製代碼

janejacky

真是謝謝
還要多學學
:$

又發現不順
出貨單的單號如何一樣同一天no.也都只有一個
單號會一樣 所以出現的出貨單歷史統計上總額也會依據上一張的總額
請問要如何怎麼處理?

還有忘記有一個稅額未加上去
請幫忙
謝謝

janejacky

  又發現問題了