waterfox

ÁÂÁ§AªºÀ°¦£, §Ú¤µ¤Ñ²×©ó¦³®É¶¡¹Á¸Õ... ¦ý¦³¤Ö¤Ö°ÝÃD, ¤wªþ¤Wµ¹¤j¤j¬d¬Ý, ­n¨C­Ó¤ë³£¦³¤@­ÓWorksheet stock list, ¦]¬°¤£¤~, ¬G¦¹§Ú·|¥ý±N©Ò¦³data©ñ¦bsheets("Stock on hand"), µM«á¦A±N¸ê®Æ¶×¤Jsheets("Summary"), ¦ý¥Î¤F§Aªº¤èªk«á,  ¦pColumn A ¤ÎColumn B¬Û¦Pªº¸Ü.. ²Ä¤G¦æªº¸ê®Æ«K©ñ¶i¤F¹jÂ÷ªº¤ë¥÷, ¦ý¥i§_¦pcolumn A, column B, column C and column D ­n4­Ó¸ê®Æ¬Û¦P«K¦b¸Ó¤ë¥÷column´¡¤J¸ê®Æ, §_«h¦b¤U¤@¦æinsert row, µM«á´¡¤J»Pcolumn A ¤ÎB ¬Û¦Pªº¸ê®Æ,

¨ä¹ê³Ì²×ªº¸ê®Æ·|©ñ¦bsummary sheets, «K¥iª¾¹D¥þ¦~stock in and out movement, ÁÂÁ§AªºÀ°¦£
record.zip (105.42 KB)

Stock record
¤U¸ü¦¸¼Æ: 5

2012-6-6 21:48

hugh0620

¦^´_ 11# waterfox


    Sorry~ ³Ìªñ¤u§@¤ñ¸û¦£~ ©Ò¥H~ ¤ñ¸û¨S¦³®É¶¡¤W¨Ó~
    ±zªº°ÝÃD¥D­n¬O±N[Stock on hand]©ßÂà¨ì[Summary]¤¤~
    ¥i¸Õ¸ÕªþÀÉ~  ¦ý¬OÁÙ¦³¤@­Ó²Ä¤è¨S¦³¼g­ò~
    ´N¬O§A¦b[Stock on hand] ¦³¤@­Ó¶µ¥Ø99 ¦b [Summary] ¬O¨S¦³ªº
    ¦]¬°¤£ª¾¹D§A¾ã­Ó¾Þ§@ªºÅÞ¿è~ ©Ò¥H~ ¥ý§¹¦¨°w¹ï±N¸ê®Æ©ßÂà~
    ¦A¨Ó¤@¨B¤@¨Bªº¨Ó½Õ¾ã~ §A©Ò»Ý­nªº~
      record-TEST.rar (81.28 KB)

¤U¸ü¦¸¼Æ: 7

2012-6-12 11:23

waterfox

Hugh0620, ¯u¬O«Ü·P¿E§AªºÀ°¦£, ·P¿E·P¿E, §Ú¥H¬°ÉN¤H²z§Ú, §Ú¤w¸g¦Û¤v¥Î¤@­Ó«Ü¤jªºµ{§Ç¥h§¹¦¨, ¤£¹L§Ú·|¸Õ¥Î§AÀ°¦£½s¼gªºªþÀÉ¡A¥Ñ©ó§ÚÅv­­°ÝÃD, ²{¦b¤´¥¼¦³¸ê®æ¶}±ÒÀ£ÁYÀÉ...¿ð¨Ç§Ú·|¦A¹Á¸Õ, ¦A¦¸·P¿E§A°Ú...

hugh0620

¦^´_ 13# waterfox

  ³o¬O§Ú¤W¸üÀɮפ¤ªºµ{¦¡½X~

1. Private Sub CommandButton1_Click()   '¸ê®Æ©ßÂà
   
2. '========  A =>¨C¤@¦C¶]ªº¦¸¼Æ  ====================
   
3. A = Sheet4.Range("IV2").End(xlToLeft).Column
   
4. A = (A - 4) / 3
   
5. If A - Int(A) > 0 Then
   
6.    MsgBox "¸ê®ÆÄæ¦ì¦³»~"
   
7.    Exit Sub
   
8. End If
   
9. A = A - 1
   
10. '========  B => Á`¦@­n¶]ªºµ§¼Æ  ====================
    
11. B = Sheet4.Range("A65536").End(xlUp).Row
    
12. 
    
13. For I = 3 To B
    
14.     Set C = Sheet2.Columns("B:B").Find(Sheet4.Range("B" & I))
    
15.    
    
16.     If Not C Is Nothing Then
    
17.     C = Sheet2.Columns("B:B").Find(Sheet4.Range("B" & I)).Row
    
18. 
    
19.     For J = 0 To A    '(EX. 0->11 =12¦¸)
    
20.         D = Sheet4.Cells(I, 7 + J * 3)
    
21.         If D = "" Then
    
22.         Else
    
23.         Do Until Sheet2.Range("F" & C + K) = ""
    
24.            If Sheet2.Range("F" & C + K) = D Then
    
25.               If Sheet4.Cells(I, 5 + J * 3) > 0 Then
    
26.               Sheet2.Cells(C + K, 7 + J) = Sheet4.Cells(I, 5 + J * 3)
    
27.               End If
    
28.               If Sheet4.Cells(I, 6 + J * 3) > 0 Then
    
29.               Sheet2.Cells(C + K, 21 + J) = Sheet4.Cells(I, 6 + J * 3)
    
30.               End If
    
31.               P = 1
    
32.               Exit Do
    
33.            End If
    
34.            K = K + 1
    
35.         Loop
    
36.         If P = 0 Then
    
37.            Sheet2.Rows(C + K).Insert Shift:=xlDown
    
38.            Sheet2.Range("F" & C + K) = Sheet4.Cells(I, 7 + J * 3)
    
39.            If Sheet4.Cells(I, 5 + J * 3) > 0 Then
    
40.             Sheet2.Cells(C + K, 7 + J) = Sheet4.Cells(I, 5 + J * 3)
    
41.            End If
    
42.            If Sheet4.Cells(I, 6 + J * 3) > 0 Then
    
43.             Sheet2.Cells(C + K, 21 + J) = Sheet4.Cells(I, 6 + J * 3)
    
44.            End If
    
45.         End If
    
46.         End If
    
47.     K = 0
    
48.     P = 0
    
49.     Next
    
50.     End If
    
51. Next
    
52. 
    
53. End Sub

½Æ»s¥N½X