ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA ¶ñ¼gªí®æ¤Î¥t¦s·sÀÉ

[µo°Ý] VBA ¶ñ¼gªí®æ¤Î¥t¦s·sÀÉ

ªþ¥ó¬O§Ú¦b¤½¥q¥t¤@¥÷±`±`­n°Å°Å¶K¶Kªº³øªí¡A¨C¦¸¦bdata ­¶¶ñ¦n¤F«á¡A´N­n§â¸ê®Æcopy ¦^report ­¶ªºªí®æ¸Ì¨Ã¥t¦s·sÀÉ¥H«K¤W¶Ç¡A§Ú§ä¤F«Ü¦h¨Ò¤l¡A¦h¼Æ³£¥Î©ó¤@¨Ç¬Û¹ï­Èªºªí®æ¡A¤£ª¾³oºØ¤£³W«hªºªí®æ¸Ó«ç»ò¼g,§Æ±æ¦U¦ì¯àÀ°À°¦£¡AÁÂÁ test Receiving Data 2020.zip (37.25 KB)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-30 09:58 ½s¿è

¦^´_ 1# lovenice831

§Ú¤]¤£ª¾¹D§Aªºªí®æ­n«ç»ò¶ñ¼g......

µo°Ý­nªþ¤W§A·Q­nªº "°õ¦æµ²ªG"

§Ú¥ý¨Ì§ÚÆ[¹îªºµ²ªG¼g¡A§A¦A¬Ý¬Ý

²£¥Íªº³ø§i·|¦bÀɮתº¦P¸ô®|¤U

¦pªþ¥ó


test Receiving Data 2020-1230.rar (67.83 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ lovenice831 ©ó 2020-12-30 10:01 ½s¿è

¦^´_ 2# n7822123

¥ýÁÂÁÂÀ°¦£¡A¦ý§Ú¤U¸ü¤£¨ìªþ¥ó

§Ú¥ý¿ý¨îµ¹§A­Ì¬Ý¬Ý

TOP

ªþ¥ó¬O§Ú¦b¤½¥q¥t¤@¥÷±`±`­n°Å°Å¶K¶Kªº³øªí¡A¨C¦¸¦bdata ­¶¶ñ¦n¤F«á¡A´N­n§â¸ê®Æcopy ¦^report ­¶ªºªí®æ¸Ì¨Ã ...
lovenice831 µoªí©ó 2020-12-29 16:36



    ¹Á¸Õ¿ý»s¤F¤@¦¸¡A§Æ±æ¯àÀ°¦£§ï¶i¡AÁÂÁ test Receiving Data 2020_01.zip (52.1 KB)

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-31 02:32 ½s¿è

¦^´_ 4# lovenice831

·Pı¸ò§Ú·Qªº®t¤£¦h§a!?~~ §AµLªk¦¬ªþ¥ó?

§Ú¶Kµ{¦¡µ¹§A¡A§A°õ¦æ"¦¬³f³øªí"§Y¥i~«á­±³£¬O°Æµ{¦¡

¿ý»sªº¥¨¶° 500¦h¦C¡AÀ³¸Ó¨S¤H¬Ý±o¤U¥h.....=.=¡A§A¥ý¸Õ¬Ý¬Ý¡A¦³º|¶ñ¦A»¡

¥t¥~¡A¦pªG§A¨S¿ìªk¤U¸ü§Úªºªþ¥ó¡A½Ð§â§Aªº³ø§i½d¥»"µy·L"­×§ï¤@¤U

§â¦C19¡B20¡B21 ³£§ï¦¨¸ê®Æ¦C¡A¦C20§O¥Î²ÊÅé®Ø½u¡A

·í¸ê®Æ¦h¾l3¦C¡A§Úªºµ{¦¡·|¦Û°Ê½Æ»s¦C20



Dim Arr
Sub ¦¬³f³øªí()
Dim Brr(), R&, K%, LotNo$
Application.ScreenUpdating = False
Arr = ['Receiving DATA'!A4].CurrentRegion
For R = 2 To UBound(Arr)
    If Arr(R, 5) <> LotNo Then ·sªí R: LotNo = Arr(R, 5): K = 0
    K = K + 1: ReDim Preserve Brr(1 To 11, 1 To K)
    Brr(1, K) = Arr(R, 9)     '³fª«¦WºÙ
    Brr(3, K) = LotNo        '§å¸¹
    Brr(4, K) = Arr(R, 7)     'ªO¸¹
    Brr(5, K) = Arr(R, 8)     '½c¸¹
    Brr(8, K) = Arr(R, 10)   '¹êª«¦¬³f
    If R + 1 > UBound(Arr) Then ¥t¦s·sÀÉ Brr: Exit For
    If Arr(R + 1, 5) <> LotNo Then ¥t¦s·sÀÉ Brr
Next R: Erase Arr: Erase Brr
Sheets("Receiving DATA").Activate
End Sub

Sub ·sªí(ByVal R)
Sheets("Receiving Report").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Lot No. " & Arr(R, 5)
[C10] = Int(Arr(R, 2))        '¦¬³f¤é´Á
[F10] = Arr(R, 2) - [C10]   '¦¬³f®É¶¡
[C11] = Arr(R, 3)                'Âd¸¹/³f¨®¨®µP
[J8] = Arr(R, 5)                    'Lot Number
[J10] = Arr(R, 11)                'PO No.
[J11] = Arr(R, 14)                'BD ­t³d¤H
End Sub

Sub ¥t¦s·sÀÉ(ByVal Brr)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
MyPath = ThisWorkbook.Path & "\"
Brr = Application.Transpose(Brr)
Rn = UBound(Brr): [G13] = Rn
If Rn >= 4 Then
  Rows("21:" & 21 + Rn - 4).Insert Shift:=4
  Rows(20).Copy
  Rows("21:" & 21 + Rn - 4).Select
  ActiveSheet.Paste
End If: [A10].Select
Application.CutCopyMode = False
[A19].Resize(Rn, 11) = Brr
[H13] = WorksheetFunction.Sum([E19].Resize(Rn))
ActiveSheet.Copy
With ActiveSheet
  .Parent.SaveAs MyPath & .Name & ".xls", xlNormal
  .Parent.Close 1
End With
ActiveSheet.Delete
End Sub


test Receiving Data 2020-1231.rar (49.93 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 5# n7822123


¤Q¤ÀÁÂÁÂÀ°¦£¡A§Ú¸Õ¸Õ¬Ý

TOP

¦^´_ 5# n7822123


§Ú¸Õ¹L¤F¡AÁÂÁ§A¡A
¦ý§Ú¤£¬O·Q­n§å¦¸¡A¦]¬°data ¤ºªº¸ê®Æ¬O·|¼W¥[¡A©Ò¥H¨C¦¸¥u·|°µ¥t¦s§ó·sªº³f¸¹³ø§i¤W¶Ç

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-31 18:09 ½s¿è

¦^´_ 7# lovenice831

¦ý¬O§Aªº¸ê®Æ¤º¦³¤£¦Pªº§å¸¹¡A¥u»Ý­n²Ä¤@­Ó§å¸¹¥Xªí®æªº·N«ä?

ÁÙ¬O¦U­Ó§å¸¹¥[°_¨Ó¡A¥X¤@­Ó³øªí¡A¦ý¬O¥k¤W¨¤¥u¶ñ²Ä¤@­Ó§å¸¹?

ÁÙ¬O¥u­n§ìDataªí¤ºªº³Ì«á¤@­Ó§å¦¸ªº¸ê®Æ¥Xªí®æ?

¦pªG§AData¤ºªº¸ê®Æ¶q«Ü¦h¡A¨C­Ó§å¦¸³£­n¥Xªí®æ¡AVBA°õ¦æªº½T·|ªá¤W¤£¤Ö®É¶¡

µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 8# n7822123


    DATA ªº¸ê®Æ·|¼W¥[¦Ó¨C¼W¥[¤@­Ó§Ú«K»Ý­n¦bREPORT ªí¤¤¶ñ¼g¹ïªº¸ê®Æ¡A¦A§âREPORT ¥t¦s·sÀɤW¶Ç¦^¤½¥q¨t²Î¡A¦ÓREPORT ¬O¥ÎReference No:¨Ó¬ö¿ý¤Î©R¦W¡A·|§_¦³»yªk¥i¥H¦bREPORT ­¶¤¤Â²¿ï Reference No:¡A«K¯à±qDATE ¤¤¦Û°Ê§ä¥X¹ïÀ³¸êÅã¥Ü¦bREPORT ¤¤? §Ú¦bºô¤W§äªº¦h¬O¥Î©ó¦P¤@¤À­¶¤Îªí®æ³£¬O³æ¤@¹ïÀ³ªº¨Ò¤l¡A³o¥÷ªí®æªº½T¬O³Â·Ð¤F¨Ç¡A¥ýÁÂÁ§AªºÀ°¦£¡AÁÂÁÂ

TOP

Reference No: ¦p¦ó¶ñ???

¦¬³f³ø§iªí¤¤¦³¨Çcheckbox,
¦b¶×¤J¸ê®Æ«á, ¬O§_À³¥ý³B²z¤Î¤Ä¿ï³o¨Çª«¥ó«á¦A¶×¥X¥t¦s???

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD