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

[µo°Ý] ½Ð°Ý­n¦p¦ó²¤Æ/§ó§ïVBA, §¹¦¨©¹«áªºOrder©O?

[µo°Ý] ½Ð°Ý­n¦p¦ó²¤Æ/§ó§ïVBA, §¹¦¨©¹«áªºOrder©O?

¥»©«³Ì«á¥Ñ boomf2 ©ó 2017-1-9 01:34 ½s¿è

§Ú°µªº «ö¶s1 «Ü²Â
¥u¯à¦¨¥\¦b·sªºSHEET²£¥Í¤F ¯S©wORDER 1 - Sheet (A©±001).

½Ð°Ý­n¦p¦ó²¤Æ/§ó§ïVBA, §¹¦¨©¹«áªº15±i Order©O?

½Ð°ª«ü¾É¤@¤U­n«ç»ò¼gvba...:'(

Order.rar (7.16 KB)

§Ú°µªº «ö¶s1 «Ü²Â
¥u¯à¦¨¥\¦b·sªºSHEET²£¥Í¤F ¯S©wORDER 1 - Sheet (A©±001).

½Ð°Ý­n¦p¦ó²¤Æ/§ó§ïVBA ...
boomf2 µoªí©ó 2017-1-9 01:31



    Order§ó·s1.rar (30.99 KB)

­ì¨Ó§Ú©ñ¿ùªþ¥ó¤F...³o­Ó¤~¹ïªº

§Ú°µªº «ö¶s1
¥u¯à¦¨¥\¦b·sªºSHEET²£¥Í¤F ¯S©wORDER 1 - Sheet (A©±001).

½Ð°Ý­n¦p¦ó²¤Æ/§ó§ïVBA, §¹¦¨©¹«áªº15±i Order©O?
¨ä¹ê§Ú·Q§â¥þ³¡15­Ó³£©ñ¦b·sWORKBOOK, ¤À¦¨15±iSHEETªº.

½Ð°ª«ü¾É¤@¤U­n«ç»ò¼gvba...:'(

TOP

¦^´_ 2# boomf2


    ­èµo²{§A¦³¨â­Ó A©±002 ½Ð°Ý·Q¦p¦ó°Ï§O

   ¦]¬°ÀɮצWºÙ¤£¯à¦³­«½Æ

TOP

¦^´_ 2# boomf2
  1.     Sub ¦Û°Ê·s¼W¤é¤u§@ï§ó·s()

  2. ¥»ÀɦW = ActiveWorkbook.Name
  3. ¥»¸ô®| = ActiveWorkbook.Path


  4. '***********************************************§ä¥Xa©±001¦ì¸m***********************************
  5. For aaa = 1 To 20
  6. On Error Resume Next
  7. ©±¦WÄæ¦ì = Sheets("Total").Rows(aaa).Find(What:="A©±001", LookIn:=xlValues, SearchDirection:=xlPrevious).Column
  8. On Error GoTo 0
  9. Next

  10. For aaa = 1 To ©±¦WÄæ¦ì
  11. On Error Resume Next
  12. ©±¦W¦C¦ì = Sheets("Total").Columns(aaa).Find(What:="A©±001", LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlPrevious).Row
  13. On Error GoTo 0
  14. Next
  15. '***********************************************§ä¥Xa©±001¦ì¸m***********************************




  16. '***********************************************§ä¥X³Ì«á¤@¶¡©±¦ì¸m***********************************
  17. ©±¦W³Ì«áÄæ¦ì = Sheets("Total").Rows(©±¦W¦C¦ì).Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlPrevious).Column
  18. '***********************************************§ä¥X³Ì«á¤@¶¡©±¦ì¸m***********************************




  19. '***********************************************§ä¥X²£«~¼Æ¶q***********************************
  20. ²£«~codeÄæ = Sheets("Total").Rows(©±¦W¦C¦ì).Find(What:="²£«~CODE", LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlPrevious).Column

  21. ²£«~code¦C = Sheets("Total").Columns(²£«~codeÄæ).Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row

  22. ²£«~Á`¼Æ = ²£«~code¦C - ©±¦W¦C¦ì
  23. '***********************************************§ä¥X²£«~¼Æ¶q***********************************


  24. '***********************************************Åý¤§«á°j°é¥Î***********************************
  25. ²£«~°_©l¦C©w¦ì = ©±¦W¦C¦ì + 1
  26. ²£«~µ²§ô¦C©w¦ì = ²£«~code¦C
  27. '***********************************************Åý¤§«á°j°é¥Î***********************************

  28. ''*************************¨ä¥L¸ê°T*************************
  29. ¤é´Á¼È¦s = Workbooks(¥»ÀɦW).Sheets("Total").[G1]

  30. ''*************************¨ä¥L¸ê°T*************************





  31. '*************************¬y¤ô¸¹°j°é°_©l­È*************************
  32. ¬y¤ô = 1
  33. '*************************¬y¤ô¸¹°j°é°_©l­È*************************

  34. While ©±¦WÄæ¦ì < ©±¦W³Ì«áÄæ¦ì + 1

  35. ©±¦W = Workbooks(¥»ÀɦW).Sheets("Total").Cells(©±¦W¦C¦ì, ©±¦WÄæ¦ì)


  36. ¥XÀɸ¹ = "order" & ¬y¤ô
  37. Set nX = Workbooks.Add


  38. Application.DisplayAlerts = False

  39. ActiveWorkbook.Sheets(3).Delete
  40. ActiveWorkbook.Sheets(2).Delete
  41. ActiveWorkbook.Sheets(1).Name = ©±¦W


  42. ²£«~¹Bºâ¦C = ²£«~°_©l¦C©w¦ì
  43. ²£«~°±ºâ¦C = ²£«~µ²§ô¦C©w¦ì

  44.     orderrow = 1
  45.     While ²£«~¹Bºâ¦C < ²£«~°±ºâ¦C + 1
  46.     If Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ©±¦WÄæ¦ì) <> "" Then
  47.    
  48.             ActiveWorkbook.Sheets(1).Cells(orderrow, 1) = "DK"
  49.             ActiveWorkbook.Sheets(1).Cells(orderrow, 3) = ¤é´Á¼È¦s
  50.             ActiveWorkbook.Sheets(1).Cells(orderrow, 4) = "DN"
  51.             ActiveWorkbook.Sheets(1).Cells(orderrow, 5) = "B99"
  52.             ActiveWorkbook.Sheets(1).Cells(orderrow, 6) = "©±¦W"
  53.             ActiveWorkbook.Sheets(1).Cells(orderrow, 7) = Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ²£«~codeÄæ).Value
  54.             ActiveWorkbook.Sheets(1).Cells(orderrow, 8) = Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ²£«~codeÄæ + 3).Value
  55.             ActiveWorkbook.Sheets(1).Cells(orderrow, 9) = Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ©±¦WÄæ¦ì)
  56.             orderrow = orderrow + 1
  57.     End If
  58.    
  59.    
  60.     ²£«~¹Bºâ¦C = ²£«~¹Bºâ¦C + 1
  61.     Wend

  62. nX.SaveAs ThisWorkbook.Path & "/" & ¥XÀɸ¹ & ©±¦W
  63. nX.Close

  64. ¬y¤ô = ¬y¤ô + 1
  65. ©±¦WÄæ¦ì = ©±¦WÄæ¦ì + 1
  66. Wend


  67. End Sub
½Æ»s¥N½X

TOP

©±¦W¦C¦ì = Sheets("Total").Columns(aaa).Find(What:="A©±001", LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlPrevious).Row

Look at:=xlwhole
ªº¥Îªk¬O­n§¹¥þ¤ñ¹ï¡A§Ú¦³¤@¦æ¨Ã¨S¦³¥´¨ì
¦ý·Q¤F¤@¤U¥´¤F§¹¥þ¤ñ¹ï¦n¹³¤]¨S¥Î

¦]¬°§Ú·Q¨ì©pªº©±¦W¦³¥i¯à­«½Æ
¨º¤@¶}©lªº©w¦ì ©Î³\§A­n§ä¤@¤U¨ä¥L¤è¦¡
©ÎªÌ¥Îselection.rangeªº¤è¦¡
Àx¦s¦¨ÅܼơA¿ï¦n°_©lÄæ¦ì
¦b§ìÄæ¦C­È
¦A¶Çµ¹«á­±ªºµ{¦¡½X¨Ï¥Î

TOP

  1. Sub TEST()
  2. Dim R%, C&, xDate$, Arr, Brr, Crr, i&, j&, N&
  3. Dim xB As Workbook, xS As Worksheet, SS%
  4. R = Cells(Rows.Count, "D").End(xlUp).Row - 9 '¸ê®Æ¦C¼Æ(§t¼ÐÃD¦C)
  5. C = Cells(10, Columns.Count).End(xlToLeft).Column - 15 '(OrderÄæ¼Æ)
  6. If R <= 0 Or C <= 0 Then Exit Sub
  7. xDate = [G1] '¤é´Á
  8. Arr = [D10].Resize(R) '²£«~CODE
  9. Brr = [P10].Resize(R, C) 'Order¸ê®Æ°Ï
  10. Set xB = Workbooks.Add '¶}·sÀÉ®×
  11. For i = C To 1 Step -1
  12.     If Brr(1, i) = "" Then GoTo 101
  13.     Set xS = xB.Sheets.Add: xS.Name = Brr(1, i) '·s¼W¤u§@ªí
  14.     xS.[C:C].ColumnWidth = 11 '¤é´ÁÄæ¼e
  15.     N = 0: SS = SS + 1 '·s¼W¤u§@ªí²Ö­p±i¼Æ
  16.     For j = 2 To R
  17.         If Brr(j, i) <> "" Then
  18.            N = N + 1
  19.            xS.Cells(N, 1).Resize(1, 9) = Array("DK", "", "'" & xDate, "DN", "B99", Brr(1, i), Arr(j, 1), "", Brr(j, i))
  20.         End If
  21.     Next
  22. 101: Next i

  23. Application.DisplayAlerts = False
  24. If SS > 0 Then
  25.    For i = xB.Sheets.Count To SS + 1 Step -1
  26.        xB.Sheets(i).Delete '§R°£·sÀÉ®×¹w³]ªÅ¥Õ¤u§@ªí
  27.    Next
  28. End If
  29. xB.SaveAs Filename:=ThisWorkbook.Path & "\©ú²Óªí_" & xDate & ".xls", CreateBackup:=False '¥t¦s·sÀÉ
  30. End Sub
½Æ»s¥N½X
Xl0000061.rar (16.85 KB)

TOP

¦^´_ 5# singo1232001


   ¤£¦n·N«ä,§Ú¦³»s§@½d¥»®É,¤£¤p¤ß­«ÂФF.
¬O¤£·|­«ÂЪº.

§Ú¥¿¦bºCºC¤F¸Ñ¸×¨ÇCODE...»Ý­nÂI®É¶¡...

TOP

¦^´_ 6# ­ã´£³¡ªL

ÁÂÁª©¥D ­ã´£³¡ªL.
§Ú¬Q±ß¨S«çºÎ¸Õ¤F¤@¤U§@¤@¨Ç§ï°Ê......¥i±¤§Ú¤£¦¨¥\..

§A¥i¥H¦AÀ°§Ú¬Ý¬Ý¶Ü?

Xl0000061B.rar (120.45 KB)

§Ú¦bX10000061B.xls ¸Ì
¶À¦âªº¤@¦æ:

·Q¦bVBA¥[¤J= K ªº¸Ü, ¤~·s¼W¨ìnew workbook new sheet
=fff ªº¸Ü, §Ú¥´ºâ¤§«á¦A
·s¼W¾í¦â¬O©±¾Q¦W:
§Ú·Q§â¥¦©ñ¦b©ú²Óªí¸Ì ªº "K" - ³o­Ó§Ú¸Õ¤F«Ü¤[¡K¦ý¤£¦¨¥\.


¦b©ú²Óªí¸Ì:

·Q§â­ì¥»"K"¸ÌªºBrr(1, i) ©ñ¦b "J"
¤w§ï¦¨Resize(1,10), ·s¼W¤F¤@­ÓBrr(1, i) ¨ì³Ì«á.
xS.Cells(N, 1).Resize(1, 10) = Array("DK", "", "'" & xDate, "DN", "B99", Brr(1, i), Arr(j, 1), "", Brr(j, i), Brr(1, i))

³Ì«á§Ú·Q§â ·s¼W¥X¨Óªº ©ú²Óªí¸ÌªºSheet, ¦ASAVE AS ¦¨³æ¿WFILE...

§Ú¸Õ¹LPersonal VBA project book ¥[¤J¤À³ÎSHEET¦¨XLS ªºSUB, ¥i¬O...¥u¤À©î¤F¤@­Ó¥X¨Ó...©Ç©Çªº.
½Ð«ü±Ð¤@¤U...ÁÂÁÂ

TOP

·Q¦bVBA¥[¤J= K ªº¸Ü, ¤~·s¼W¨ìnew workbook new sheet
=fff ªº¸Ü, ¥ý¤£§@¿é¥X. §Ú¥´ºâ¤§«á¦A°µ¤@¨ÇCSV®æ¦¡.

TOP

¦^´_ 9# boomf2

Xl0000061B_V1.rar (123.47 KB)

³Ìªñ¦£, ­Y¦³¨ä¥¦¸É¥R©Î­×§ï, ½Ð¨ä¥L¤j¤jÀ°¦£!

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD