[發問] 報表消帳扣除數量

home1913

請教一下各位大師，目前報表需使用到類SAP功能.
例如附件中D欄如有對應到各點，各點總量扣除購買量的剩餘數.
感謝大師們指導.
數量扣法.rar (7.02 KB)

數量扣除
下載次數: 7

2014-3-1 22:42

yen956

回復 3# home1913
試試看:

1. Option Base 1
   
2. Option Explicit
   
3. Private Sub CommandButton1_Click()
   
4.     Dim i, j, 列數, Barcode數, 原庫存量 As Integer
   
5.     Dim code1, code2, 欄名 As String
   
6.     Dim findCell, findRng As Range
   
7.    
   
8.     Barcode數 = [B1].End(xlDown).Row - 1
   
9.     '1. 插入輔助欄 欄A(手動)
   
10.    
    
11.     '2. 將 欄C 貨號合併, 且 欄A 填入遞增數列
    
12.     [C2].Resize(Barcode數, 1) = ""
    
13.     [E2].Resize(Barcode數, 1) = ""
    
14.     For i = 2 To Barcode數 + 1
    
15.         Cells(i, 1) = i
    
16.         If Left(Cells(i, 2), 1) = "w" Then
    
17.             code1 = Left(Cells(i, 2), 7)
    
18.         Else
    
19.             code2 = Left(Cells(i, 2), 6)
    
20.             Cells(i, 3) = code1 & code2
    
21.         End If
    
22.     Next
    
23.    
    
24.     '3. 按 主鍵 欄C, 次鍵 欄A 遞增排序
    
25.     [A1].Resize(Barcode數 + 1, 5).Sort _
    
26.            Key1:=Range("C1"), Order1:=xlAscending, _
    
27.            Key2:=Range("A1"), Order2:=xlAscending, _
    
28.            Header:=xlYes
    
29.               
    
30.     '4. 重新寫入公式, 防操作不小心刪掉公式
    
31.     [F3] = "=MATCH(F2,G2:G7,0)"    '0 →精確查詢, 不可省略, Lookup_array 可以依任意次序排列。
    
32.     [F6] = "=MATCH(F5,H1:L1,0)"    '1 →可省略, 找到等於或僅次於 lookup_value 的值, Lookup_array必須遞增排序
    
33.                                    '-1→不可省略, 會找到等於或大於 lookup_value 的最小值, Lookup_array必須遞減排序
    
34.     [F8] = "=INDEX(H2:L7,F3,F6)"   '傳回查詢範圍, 列與欄 的交义點的值
    
35.    
    
36.     '以上三列為 庫存表固定大小 使用,
    
37.     '若 庫存表固定大小 會變動, 請改用下列五列
    
38. '    列數 = CStr([G1].End(xlDown).Row)
    
39. '    欄名 = Chr([G1].End(xlToRight).Column + 63)
    
40. '    [F3] = "=MATCH(F2,G2:G" & 列數 & ",0)"
    
41. '    [F6] = "=MATCH(F5,H1:" & 欄名 & "1,0)"
    
42. '    [F8] = "=INDEX(H2:" & 欄名 & "7,F3,F6)"
    
43.    
    
44.     '5. 主程式
    
45.     i = 1
    
46.     Do
    
47.         i = i + 1
    
48.         If Cells(i, 3) = "" Then GoTo done1:
    
49.         [F5] = Left(Cells(i, 3), 7)        '將Barcode存到 [F5], 供 MATCH 查詢"欄"用
    
50.         [F2] = Right(Cells(i, 3), 6)       '將貨號存到 [E2], 供 MATCH 查詢"列"用
    
51.         '[F8] = 庫存表中的 庫存量
    
52.         Cells(i, 5) = [F8] - Cells(i, 4)   '存量 = 原庫存量 - 銷售量
    
53.         
    
54.         '如果 貨號 重覆, 第二筆開始, 則
    
55.         If Cells(i, 3) = Cells(i + 1, 3) Then
    
56.             Do
    
57.                 i = i + 1
    
58.                 If Cells(i, 3) = "" Then GoTo done1:
    
59.                 Cells(i, 5) = Cells(i - 1, 5) - Cells(i, 4)     '存量 = 上一筆存量 - 銷售量
    
60.             Loop Until i > Barcode數 Or Cells(i, 3) <> Cells(i + 1, 3)
    
61.         End If
    
62.     Loop Until i > Barcode數
    
63.    
    
64. done1:
    
65.     '6. 按原序排序
    
66.         [A1].Resize(Barcode數 + 1, 5).Sort _
    
67.               Key1:=Range("A1"), Order1:=xlAscending, _
    
68.               Header:=xlYes
    
69. End Sub

複製代碼

home1913

如D3.D4來說,數量=A2前7碼對應到(G1:K1)後,D3.D4需使用右表中相同貨號之數量扣除C3.C4後的數量.
而D6數量=A5前7碼對應到(G1:K1)後,扣除先前剩餘的數量(EX3-C6).

PS.條件A欄必須要和右表(G1:K1)有相對應才可扣除相同貨號的數量.
以上.感謝

sunnyso

回復 1# home1913


    可否再詳細點解說