投降了,实在琢磨不出, 库存先进先出的vba代码，谁帮看看

大氵寿

具体见附件。

弄了二天，实在是太绕脑袋了，脑袋现在全是浆糊，给绕进去了。

查了论坛很多先进先出的代码，都不涉及到多个料号，我这里是有很多重不同的料号，
先进先出，不考虑日期因素。
谁帮我检查一下代码，该怎么改?多谢了。


Book2.zip (19.87 KB, 下载次数: 15)

2014-1-28 14:29 上传

点击文件名下载附件

1. 
   
2. Option Explicit
   
3. 
   
4. Private Sub CommandButton1_Click()
   
5. Dim item, xx, i As Integer
   
6. Dim inputqty1, inputqty2, inputamt1, inputamt2, outputqty, outputqtyttl, outputamtttl As Long
   
7. Range("j2:j65536").ClearContents
   
8. 
   
9. 10: i = 2
   
10.     xx = 2
    
11.     inputqty1 = 0
    
12.     inputqty2 = 0
    
13.     inputamt1 = 0
    
14.     inputamt2 = 0
    
15.     outputqty = 0
    
16.     outputqtyttl = 0
    
17.     outputamtttl = 0
    
18. 
    
19. 11: If Len(Cells(i, 10)) > 0 Then
    
20.     i = i + 1
    
21.         If i > Range("i65536").End(xlUp).Row Then
    
22.         Exit Sub
    
23.         End If
    
24.     GoTo 11
    
25.     End If
    
26. 
    
27. 20: item = Cells(i, 5)
    
28. 30: If Cells(i, 5) <> item Then
    
29.     i = i + 1
    
30.         If i > Range("i65536").End(xlUp).Row Then
    
31.         GoTo 10
    
32.         End If
    
33.     GoTo 30
    
34.     End If
    
35. 
    
36.     outputqty = Cells(i, 9)
    
37.     outputqtyttl = outputqtyttl + outputqty
    
38. 
    
39. 50:     inputqty2 = inputqty1
    
40.         inputamt2 = inputamt1
    
41. 
    
42. 60:     With Sheets("INPUT")
    
43.         If .Cells(xx, 4) = item Then
    
44.         inputqty1 = inputqty1 + .Cells(xx, 8)
    
45.         inputamt1 = inputamt1 + .Cells(xx, 9)
    
46.         Else
    
47.         xx = xx + 1
    
48.             If xx > .Range("H65536").End(xlUp).Row Then
    
49.                 i = i + 1
    
50.                 GoTo 30
    
51.             End If
    
52.         GoTo 60
    
53.         End If
    
54. 
    
55.         If outputqtyttl <= inputqty1 Then
    
56.             Cells(i, 10) = (1 - (inputqty1 - outputqtyttl) / .Cells(xx, 8)) * .Cells(xx, 9) + inputamt2 - outputamtttl
    
57.             outputamtttl = outputamtttl + Cells(i, 10)   'actually cells(i,10)=0 currently
    
58.             i = i + 1
    
59.             xx = xx + 1
    
60.                 If i > Range("i65536").End(xlUp).Row Then
    
61.                     GoTo 10
    
62.                 End If
    
63.             GoTo 30
    
64.         Else
    
65.         xx = xx + 1
    
66.         GoTo 50
    
67.         End If
    
68.         End With
    
69. GoTo 10
    
70. 
    
71. End Sub
    
72. 
    

复制代码

KCFONG

see if help you

大氵寿

KCFONG 发表于 2014-1-28 16:00
see if help you

大神,谢谢您了.

这个太搞脑子了,,,晕头转向的...
还好您帮忙,,多谢多谢!!!!!!!!!!!

KCFONG

Did my macro help you and fix your problem ??

大氵寿

KCFONG 发表于 2014-1-28 16:15
Did my macro help you and fix your problem ??

yes, terrific ,,,,

thanks so much !!!

大氵寿

KCFONG 发表于 2014-1-28 16:15
Did my macro help you and fix your problem ??

sorry , i checked the code later ,and put special case for running , the result is mistake and don't match the expectation ,

i have to review all codes ,

will be highly appreciate if you here and can help ,

thanks at advanced.

大氵寿

please find attachment and unzip it , originally use your code ,but incorrect result ,

大氵寿

自己解决了,
下面是最终代码:

1. 
   
2. Private Sub CommandButton1_Click()
   
3. On Error Resume Next
   
4. Dim i, x As Integer
   
5. Dim Item As String
   
6. Dim itemQty, OutQty, OutAmt, InQty, InAmt As Double
   
7. Range("j2:J65536").ClearContents
   
8. 
   
9. For i = 2 To Range("I65536").End(xlUp).Row
   
10.     Item = Cells(i, 5)
    
11.     itemQty = Cells(i, 9)
    
12.     OutQty = Application.WorksheetFunction.SumIf(Range("E1:I" & i - 1), Item, Range("i1:i" & i - 1))
    
13.     OutAmt = Application.WorksheetFunction.SumIf(Range("E1:J" & i - 1), Item, Range("j1:J" & i - 1))
    
14.    
    
15.     With Sheets("INPUT")
    
16.     For x = 2 To .Range("H65536").End(xlUp).Row
    
17.         If .Cells(x, 4) = Item Then
    
18.         InQty = InQty + .Cells(x, 8)
    
19.         InAmt = InAmt + .Cells(x, 9)
    
20.             If InQty >= OutQty + itemQty Then
    
21.             Cells(i, 10) = (InAmt - (.Cells(x, 9) * (InQty - OutQty - itemQty) / .Cells(x, 8))) - OutAmt
    
22.             Exit For
    
23.             End If
    
24.         End If
    
25.     Next
    
26.     End With
    
27.     InQty = 0
    
28.     InAmt = 0
    
29. Next
    
30. End Sub
    

复制代码

mmwwdd

透英文，呵呵