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

[µo°Ý] ·j´M§Ç¸¹¹ïÀ³ªº¤u§@ªí¡A¨Ã±N¥Ø¼Ð±a¦^·j´M¦C¤¤

[µo°Ý] ·j´M§Ç¸¹¹ïÀ³ªº¤u§@ªí¡A¨Ã±N¥Ø¼Ð±a¦^·j´M¦C¤¤

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-11-23 07:08 ½s¿è

¦U¦ì«e½ú¦n¡G

Ãö©ó¦p¦ó¨Ì§Ç·j´MÄæ¦ì¸ê®Æ(§Ç¸¹)¨Ã±N¥Ø¼Ð¤u§@ªíªºÄæ¦ì¥N¨ì«ü©w·j´M¦Cªº«ü©w¦ì¸m¡A¤p§Ì¤£¤~¤w¸gÁ{¼±¦hµ§½d¨ÒÁÙ¬O¤£ª¾±q¦ó¤U¤â~

¥Ø¼Ð¦p¤U:
§â¹w©w­n·j´Mªº§Ç¸¹¨Ì§Çkey¤JÄæ¦ì¡A¦p¹Ï¡G


«ö¤U ¡i¡õ§Ö³t§Ç¸¹¬d¸ß¡j«á¡G
«h±N¹ïÀ³¤u§@ªí¤º§Ç¸¹ªºÄæ¦ì½Æ»s¦^·j´Mªº«ü©wÄæ¦ì¡A¦p¹Ï¡G


¥dÃöÂI¦b©ó¡A
1.¤£ª¾¹D«ç»ò§P§O¥Ø¼Ð§Ç¸¹ªº¤u§@ªí¦WºÙ
2.±N¥Ø¼Ð½Æ»s¦^·j´M¦Cªº«ü©wÄæ¦ì«D½u©Êªº³B²z¤è¦¡

¸Ô²Ó¥Ø¼ÐÀɮ׽аѾ\ªþ¥ó¤U¸ü¡G 1122§Æ±æ§¹¦¨¼Ë¦¡(ª©¥»10).zip (79.71 KB)
§Æ±æ¦U¦ì¤j¤j¯à°÷À°¦£«üÂI°g¬z~~¦³³Ò«e½ú¶O¤ß¤FÁÂÁ :Q

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2015/11/26 05:09): °Ñ¦Ò³o¸Ì http://forum.twbts.com/thread-15690-1-1.html

·PÁ GBKEE ª©¤j¸òYEN¤j¹©¤O¨ó§U~¬Q¤Ñ¸Õ§¹«á¥\¯à§¹¥þ¥¿±`

¤£¹L©_©Çªº¬O¡A¤µ¤Ñ«ç»ò¸Õ³£·|¤@ª½¥X²{"·¸¦ì"ªº°T®§¡A¤£ª¾¹Dµo¥Í¬Æ»ò¨Æ¤F¡A



ªþ¤W¡A¥X²{°ÝÃDªº­ì©lÀÉ~
1125§Æ±æ§¹¦¨¼Ë¦¡(ª©¥»12).zip (124.45 KB)

§Æ±æ¯à¦³¾÷·|¸Ñ¨M¤F~¤£³Ó·P¿E~~

TOP

·PÁ GBKEE ª©¤jªº«ü¥¿, ¤è¯à§¹¦¨
  1. '¨C­Ó[§Ç¸¹]¦b¾ã­ÓWorkBookªº²Ä3­¶¥H«á, ¬O§_§¡¥u¥X²{¤@¦¸?¤£·|­«ÂÐ?
  2. '³o­ÓVBA Code¬O¥H¤£­«ÂЬ°·Ç¦Ó¼g
  3. Sub §Ö³t§Ç¸¹¬d¸ß()
  4.     Dim D As Object, Sh As Worksheet, fRng As Range, sRng As Range
  5.     Dim I As Integer, J As Integer, sNum As String, FstAddr As String
  6.     Set Sh = Sheets("¥X³f¿é¤J")
  7.     For I = 5 To Sh.[B5].End(xlDown).Row
  8.         sNum = Sh.Cells(I, 2)
  9.         For J = 3 To Sheets.Count
  10.             Sheets(J).Activate
  11.             Set sRng = Sheets(J).Range("B3", "B" & [B2].End(xlDown).Row)   '³]©w³Q·j´MªºRange
  12.             sRng.Select
  13. '            On Error Resume Next
  14.             Set fRng = sRng.Find(sNum, lookat:=xlWhole)   '¦bSheets(J)ªºsRng¤¤´M§ä §Ç¸¹
  15.             If Not fRng Is Nothing Then    '¦³§ä¨ì
  16.                 Sh.Cells(I, 1) = fRng.Offset(0, -1)   '«þ¨©¬ÛÃö¸ê®Æ
  17.                 Sh.Cells(I, 3) = fRng.Offset(0, 1)
  18.                 fRng.Offset(0, 3).Select
  19.                 fRng.Offset(0, 3).Resize(1, 4).Copy Sh.Cells(I, 4)
  20.                 '§Aµ¹ªº¹Ï»P´ú¸ÕÀɦ³¥X¤J, ¹Ï¦³[¥X³f/­É¥X¤é´Á],¦Ó´ú¸ÕÀɪº[¥X³f/­É¥X¤é´Á]¬OªÅªº?
  21.                 GoTo Next1      '«þ¨©§¹´«¤U¤@­Ó§Ç¸¹
  22.             End If
  23.         Next
  24. Next1:
  25.     Next
  26.     Sh.Activate
  27. End Sub
½Æ»s¥N½X
test.gif

TOP

¤j¤j§A¦n, ¨S¿ù, §â else, exit for ¥h±¼´N¹ï¤F,
exit for ¥»¨Ó´N·|¸õ¥X¦Û¤vªº¦^°é, ÁÂÁ«ü±Ð!!

TOP

¦^´_ 1# yen956
  1. Sub §Ö³t§Ç¸¹¬d¸ß()
  2.     Dim D As Object, Sh As Worksheet, fRng As Range, sRng As Range
  3.     Dim I As Integer, J As Integer, sNum As String, FstAddr As String
  4.     Set Sh = Sheets("¥X³f¿é¤J")
  5.     For I = 5 To Sh.[B5].End(xlDown).Row
  6.         sNum = Sh.Cells(I, 2)
  7.         For J = 3 To Sheets.Count
  8.             Sheets(J).Activate
  9.             Set sRng = Sheets(J).Range("B3", "B" & [B2].End(xlDown).Row)   '³]©w³Q·j´MªºRange
  10.             sRng.Select
  11.            ' On Error Resume Next
  12.             Set fRng = sRng.Find(sNum, lookat:=xlWhole)   '¦bSheets(J)ªºsRng¤¤´M§ä §Ç¸¹
  13.             If Not fRng Is Nothing Then    '¦³§ä¨ì
  14.                 Sh.Cells(I, 1) = fRng.Offset(0, -1)   '«þ¨©¬ÛÃö¸ê®Æ
  15.                 Sh.Cells(I, 3) = fRng.Offset(0, 1)
  16.                 fRng.Offset(0, 3).Select
  17.                 fRng.Offset(0, 3).Resize(1, 4).Copy Sh.Cells(I, 4)
  18.                 '§Aµ¹ªº¹Ï»P´ú¸ÕÀɦ³¥X¤J, ¹Ï¦³[¥X³f/­É¥X¤é´Á],¦Ó´ú¸ÕÀɪº[¥X³f/­É¥X¤é´Á]¬OªÅªº?
  19.                 GoTo Next1  '«þ¨©§¹´«¤U¤@­Ó§Ç¸¹       '-> ¦A­«·s¦^¨ì²Ä3­Ó¤u§@­¶·j´M¤U¤@­Ó§Ç¸¹
  20.                 '********³o­ÓVBA Code¬O¥H¤£­«ÂЬ°·Ç¦Ó¼g****               
  21.                 'Else        '¥iµù¸Ñ±¼
  22.                 ' Exit For   '§_«h´«¤U¤@­¶Ä~Äò¬d¸ß
  23.             End If
  24.             '¨S¦³§ä¨ì ·|Ä~Äò¤U¤@­Ó¤u§@·~·j´M
  25.         Next
  26. Next1:
  27.     Next
  28. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

exit for ªº§x´b?

½Ñ¦ì¤j¤j§A¦n!!
¦³¨â­Ó For   Next °j°é
Exit For ¦b²Ä2­Ó°j°é¤º,
¬°¦ó  Exit For °õ¦æ§¹·|¸õ¨ì²Ä1­Ó°j°é?
§Æ±æ¤j¤j¯à¸Ñ´b, ÁÂÁÂ!!
test.gif
1122§Æ±æ§¹¦¨¼Ë¦¡.rar (103.1 KB)

TOP

G¤j~©êºp§Ú¸Õ¤£¥X¨Ó­C ¬Ý¤£À´±z«á¨Ó§ïªºµ{¦¡½X
ª½¤W¤§«á·|¸õ¥X³o­Ó¿ù»~


§ÚÀ³¸Ó­n«ç»ò½Õ¾ã©O~

TOP

¦^´_ 1# citizen0923


¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex_§Ç¸¹¬d¸ß()
  3.     Dim D As Object, Sh As Worksheet, Rng As Range, x As Variant
  4.     Set D = CreateObject("scripting.dictionary")   '¦r¨åª«¥ó
  5.     For Each Sh In Sheets
  6.         If Sh.Name <> "¬d¸ß" And Sh.Name <> "¥X³f¿é¤J" Then '¥Ø¼Ð§Ç¸¹ªº¤u§@ªí
  7.             Set Rng = Sh.Range("a3")
  8.             Do While Rng <> ""  '§Ç¸¹="" ®É¤£°õ¦æ°j°é
  9.                 x = Rng.Offset(, 1).Value  '§Ç¸¹
  10.                 D(x) = Rng.Resize(, Sh.UsedRange.Columns.Count) '§Ç¸¹©Ò¦b¦CªºÀx¦s®æ
  11.                 Set Rng = Rng.Offset(1) '¤U¤@­Ó§Ç¸¹
  12.             Loop
  13.         End If
  14.     Next
  15.     Set Sh = Sheets("¬d¸ß")
  16.     Set Rng = Sh.Range("a5")
  17.     Do While Rng.Offset(, 1) <> ""
  18.         x = D(Rng.Offset(, 1).Value)  '¾É¤J KEY ªº¶µ¥Ø(¤º®e)
  19.         Rng.Resize(, Sh.UsedRange.Columns.Count) = Array(x(1, 1), x(1, 2), x(1, 3), x(1, 5), x(1, 6), x(1, 7), x(1, 8))
  20.         Set Rng = Rng.Offset(1)
  21.     Loop
  22. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD