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

[µo°Ý] VBA ¦p¦ó·s¼W¤U¤@µ§¸ê®Æ

[µo°Ý] VBA ¦p¦ó·s¼W¤U¤@µ§¸ê®Æ

·Q½Ð°Ý¦U¦ìVBA°ª¤â¡A¤p§Ì§Ú¬O³Ìªñ¶}©l¬ã¨sVBAµ{¦¡
¥Ø«e¬O·Q­n¼¶¼g¦b¯àExcelªí³æ¤º·s¼W¤@µ§¸ê®Æ«á
·|¤@ª½©¹¤U·s¼W
¥Ø«e¤p§Ì¼gªºµ{¦¡¦p¤U
¦ý¨C¼g¤J¤@µ§¸ê®Æ«á´N·|§R°£«e¤@µ§¸ê®Æ
Äæ¦ì¤£¾å±o¦p¦ó©¹¤U·s¼W?
===================================
Sub °}¦C·s¼W¸ê®Æ()
Dim a(18)
Sheets("ªÅ¥Õ»Ý¨D³æ").Select
a(0) = Range("B29") '½s¸¹
a(1) = Range("A1") '»Ý¨D¶µ¥Ø
a(2) = Range("B2") '¤l¶µ¥Ø
a(3) = Range("B3") '©e°U³æ¦ì
a(4) = Range("B4") '©e°U¤H
a(5) = Range("B5") '©e°U¶µ¥Ø
a(6) = Range("D4") '³sµ¸¤è¦¡
a(7) = Range("F3") '©e°U¤é
a(8) = Range("F4") '¥Ø¼Ð§¹¦¨¤é
a(9) = Range("F5") '¹ê»Ú§¹¦¨¤é
a(10) = Range("B6") '®×¥ó½t¥Ñ
a(11) = Range("B8") '»s§@¤è¦¡
a(12) = Range("B11:B13") '¥²³Æ¶µ¥Ø
a(13) = Range("B14") '¦^õX¶µ¥Ø
a(14) = Range("C16:C18") '¦^õX®æ¦¡
a(15) = Range("B20") '»s§@¶¶§Ç
a(16) = Range("B22") '¸ê®Æ¦ì¸m
a(17) = Range("B24") '«áÄò°lÂÜ
a(18) = Range("B26") '³Æµù

Sheets("½u¤WÁ`ªí").Select
Range("S3") = a(0) '½s¸¹
Range("A3") = a(1) '»Ý¨D¶µ¥Ø
Range("B3") = a(2) '¤l¶µ¥Ø
Range("C3") = a(3) '©e°U³æ¦ì
Range("D3") = a(4) '©e°U¤H
Range("E3") = a(5) 'Ápµ¸¤è¦¡
Range("F3") = a(6) '³sµ¸¤è¦¡
Range("G3") = a(7) '©e°U®É¶¡
Range("H3") = a(8) '¥Ø¼Ð§¹¦¨®É¶¡
Range("I3") = a(9) '¹ê»Ú§¹¦¨®É¶¡
Range("J3") = a(10) '®×¥ó½t¥Ñ
Range("K3") = a(11) '»s§@¤è¦¡
Range("L3") = a(12) '¥²­n¶µ¥Ø
Range("M3") = a(13) '¦^õX¶µ¥Ø
Range("N3") = a(14) '¦^õX®æ¦¡
Range("O3") = a(15) '»s§@¶¶§Ç
Range("P3") = a(16) 'Àɮ׸ô®|
Range("Q3") = a(17) '«áÄò°lÂÜ
Range("R3") = a(18) '³Æµù

Sheets("ªÅ¥Õ»Ý¨D³æ").Select
Range("A1,B2,B3,B4,B5,D4,F3,F4,F5,B6,B8,B11:B13,B14,C16:C18,B20,B22,B24,B26").Select
Selection.ClearContents '·s¼W§¹¤@µ§«á·|§R°£ªí³æ¤ºªº¸ê®Æ¨Ã±N½s¸¹+1

Range("B29").Formula = "=Text(" & a(0) + 1 & ",""0000"")"

End Sub
==========================================================
Jared

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-2-21 16:03 ½s¿è

¦^´_ 1# Jared
a(12) = Range("B11:B13") '¥²³Æ¶µ¥Ø
a(14) = Range("C16:C18") '¦^õX®æ¦¡
¦¹2­Ó¶µ¥Ø¬O¦X¨ÖÀx¦s®æ¶Ü?
³Ì¦n¤W¶ÇÀÉ®×»¡©ú
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

§A¦n!
a(12) = Range("B11:B13") '¥²³Æ¶µ¥Ø
a(14) = Range("C16:C18") '¦^õX®æ¦¡

B11¡BB12¡BB13¬O³sÄòÄæ¦ì
¬O¤@¶ôÀx¦s¦b¥t¥~¤@­ÓÄæ¦ì
Jared

TOP

¦^´_ 3# Jared
¨S¦³ÀɮסA²q²q§Aªº»Ý¨D
  1. Sub °}¦C·s¼W¸ê®Æ()
  2. Dim a(1 To 19)
  3. With Sheets("ªÅ¥Õ»Ý¨D³æ")
  4. a(1) = .Range("A1") '»Ý¨D¶µ¥Ø
  5. a(2) = .Range("B2") '¤l¶µ¥Ø
  6. a(3) = .Range("B3") '©e°U³æ¦ì
  7. a(4) = .Range("B4") '©e°U¤H
  8. a(5) = .Range("B5") '©e°U¶µ¥Ø
  9. a(6) = .Range("D4") '³sµ¸¤è¦¡
  10. a(7) = .Range("F3") '©e°U¤é
  11. a(8) = .Range("F4") '¥Ø¼Ð§¹¦¨¤é
  12. a(9) = .Range("F5") '¹ê»Ú§¹¦¨¤é
  13. a(10) = .Range("B6") '®×¥ó½t¥Ñ
  14. a(11) = .Range("B8") '»s§@¤è¦¡
  15. a(12) = Join(Application.Transpose(.Range("B11:B13")), "¡B") '¥²³Æ¶µ¥Ø
  16. a(13) = .Range("B14") '¦^õX¶µ¥Ø
  17. a(14) = Join(Application.Transpose(.Range("C16:C18")), "¡B") '¦^õX®æ¦¡
  18. a(15) = .Range("B20") '»s§@¶¶§Ç
  19. a(16) = .Range("B22") '¸ê®Æ¦ì¸m
  20. a(17) = .Range("B24") '«áÄò°lÂÜ
  21. a(18) = .Range("B26") '³Æµù
  22. a(19) = .Range("B29") '½s¸¹
  23. With Sheets("½u¤WÁ`ªí")
  24. .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 19) = a
  25. End With
  26. .Range("A1,B2,B3,B4,B5,D4,F3,F4,F5,B6,B8,B11:B13,B14,C16:C18,B20,B22,B24,B26").ClearContents '·s¼W§¹¤@µ§«á·|§R°£ªí³æ¤ºªº¸ê®Æ¨Ã±N½s¸¹+1
  27. .Range("B29").Formula = "=Text(" & a(19) + 1 & ",""0000"")"
  28. End With
  29. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁÂ Hsieh
°¨¤W¨Ó¸Õ¬Ý¬Ý~
Jared

TOP

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