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

¤£¦n·N«ä ·s¤â¤S¨Ó½Ð±Ð ¸ê®Æ¤ñ¹ï°ÝÃD ³Â·Ð¦U¦ì¤F

vba ¤ñ¹ï¸ê®Æ°ÝÃD

¤£¦n·N«ä ¤p§Ì³o°ÝÃD¤w¸g§xÂZ«Ü¤[ ¹ê¦b·Q¤£¥X¨Ó ©Ò¥H¤W¨Ó¨D±Ï ,ÁÂÁ¤j®a¼·®É¶¡À°§Ú¬Ý

¦³¨â­ÓexcelÀÉ,¦pªþ¥ó

book1¸Ì­±ªºc sheet ¬O§Ú·Q­n«Ø¥ß¤@­Óbuttonªº¦a¤è,«ö¤U¥h¤§«á¯à°÷°µ¥H¤U°Ê§@

¥Øªº¬O§Æ±æ¯à°÷§âbook2 (b sheet) ¸Ì­±ªº¸ê®Æ §ì¨ìbook1ªº (a sheet ),

¤]´N¬O§âÃC¦â¼Ð¥X¨Óªº¦a¤èbook2.b.sheet,COPY¨ìbook1.a.sheet

ªþÀɪºÃC¦â¬O§Ú«á¨Ó¥[¤W¥hªº,¥u¬O¬°¤F¤è«K´y­z ,

part_id ¶ñ§¹¤F©¹¥kÃäÀx¦s®æshift¤@¦ì ¨Ì¦¹Ãþ±À,

ope_no¥u»Ý§ì«á¤T½X «e«á»Ý­n©T©w¥Î(),¥Î,¸¹¹j¶},¨C­Ó¼Æ¦r³£­n¦³ ' '

¤º®e¤w¸g²¤Æ,¹ê»Ú¤Wªºtable¸ê®ÆÆZ¦h,¤£³æ¥u¬O¨âµ§, §Ú»{¬°­n¥Îfor°j°é ¦ýÁÙ«Ü¹à ¤£¤Ó¼ô ½Ð±Ð¦U¦ì¤F

¥H¤W  ·P¿E¤£ºÉ
question.rar (4.6 KB)

TOP

½Ð°ÝªO¤j ¤£¦n·N«ä ¦A½Ð±Ð¤@­Ó°ÝÃD ³o¼Ëªº»Ý¨D§Ú¥i¥H°Ñ¦Ò®ÑÄy¤¤ªº­þ¨Çfunction ? ÁÂÁÂ

TOP

¦^´_ 2# ffntldj
¯S®íªº»Ý¨D ­n¦Û­q¨ç¼Æ §A1 ¼Óªº»Ý¨D »¡ªº¤£²M·¡  book2  bÄæ ¬O¹J¨ìope_noªº¤U¤@¦C¨ú3½X«áªº¶Ü?
a,d,e.f ¦p¦ó¨Óªº
a        111
part_id        12345
ope_no        ('333','444','555','777')
d        444
e        555
f        666
f        777

TOP

ªO¤j§A¦n~adef ¥u¬O§Ú°²³]¦³¨ä¥LªºÄæ¦ì¤w¸g¦bbook1¸Ì­±,¦bbook1¤¤¥D­n¬O¥h¬Ýope_no ¸òpart_id ³o¨â­ÓÀx¦s®æ¥k¤âÃ䪺¸ê®Æ(parit-id---->12345, ope_no ---> 333 444 555 777),³o¨Ç¸ê®Æ¬O±qbook2¸Ì­±ªºb sheet¨Óªº,¥u¬O¦bbook2 copy¹L¨Ó¤§«á ¦bbook1­n°µ¤@¨Ç­×§ï ,ope_no ´N¬O­nÅܬ° ('333','444','555','777')¥[¤W¬A©·,³r¸¹©M¤À¸¹
partid «h¬O¦pªG¦bbook2¸Ì­±¦³¤@­Ó¥H¤Wªºpart_id¥X²{®É ,¦b¶ñ¤Jbook1ªº®É«á,­nÅܦ¨  part id  12345   5678 (5678­n¶ñ¦b12345®ÇÃ䨺­ÓÀx¦s®æ)      

¤£ª¾¹D³o¼Ë¦³²M·¡¶Ü? ÁÂÁ§A §Ú¤]·|¦Û¤v¦b¸ÕµÛ¼g~

TOP

¦^´_ 4# ffntldj
ÁÙ¬O¤£²M·¡ ,½d¨Ò½Ð¤£­nÀH«K¦CÁ|

TOP

¦^´_ 4# ffntldj


    ´N³æ¯Â§âbook2ªºb¤u§@ªí¤¤part_id¹ïÀ³modify«á3½X¶ñ¤Ja¤u§@ªíªº²Ä2¡B3¦C
  1. Sub nn()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. With Workbooks.Open("D:\book2.xls") 'Àɮ׸ô®|¦Û¦æ­×§ï
  4. With .Sheets("b")
  5. For Each a In .Range(.[A1], .[A65536].End(xlUp))
  6.   If a = "part_id" Then mystr = a.Offset(, 1).Text
  7.   If a = "modify" Then
  8.      If d(mystr) = "" Then
  9.         d(mystr) = "'" & Right(a.Offset(, 1), 3) & "'"
  10.         Else
  11.         d(mystr) = d(mystr) & ",'" & Right(a.Offset(, 1), 3) & "'"
  12.      End If
  13.   End If
  14. Next
  15. End With
  16. .Close
  17. End With
  18. With Sheets("a")
  19. k = 2
  20. For Each ky In d.keys
  21.    .Cells(2, k) = ky
  22.    .Cells(3, k) = "(" & d(ky) & ")"
  23. k = k + 1
  24. Next
  25. End With
  26. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

@¶W¯Åª©¥D   ÁÂÁ§A §Ú¦b¬ã¨s¤@¤U§Aªº¼gªk  
@ª©¥D    ©êºp §Ú¦b´y­z²M·¡¤@ÂI ÁÂÁÂ

TOP

ÁÂÁ¨â¦ìª©¥D, code§Ú¤w¸g¬Ý§¹¤F¤]¬ÝÀ´¤F,¤]¦Û¤v¸ÕµÛ°µ¤F¤@¨Ç­×§ï «D±`·PÁÂ
¦³¨â­Ó°ÝÃD·Q½Ð±Ð
²Ä¤@­Ó°ÝÃD´N¬O ¥H¤U³o¬qcode¬°¤°»ò¨S¦³¹ïÀ³ªºend if? ¬O¤£»Ý­n¶Ü?
If a = "part_id" Then mystr = a.Offset(, 1).Text
²Ä¤G­Ó°ÝÃD¬O
³o­Ócode¥D­n¬O§âpart_id ¥i¥H¸ò ope_no¥h°µ¹ïÀ³ ,¦p¤Uªí¥Ü
part_id                   y                   z
                   ('356','456')        ('556')
¦pªG§Ú·Q§ï¦¨
part_id                   y                              z
                   ('356','456','556')      
À³¸Ó¬O­×§ï¥H¤U³o¤@¬qcode,¸Õ¤F¤@¤U¤È,¥i§_¦A±Ð§Ú¤@¤U ·P¿E¤£ºÉ
If d(mystr) = "" Then
     d(mystr) = "'" & Right(a.Offset(, 1), 3) & "'"
      Else
      d(mystr) = d(mystr) & ",'" & Right(a.Offset(, 1), 3) & "'"
     End If

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-7-20 20:58 ½s¿è

¦^´_ 8# ffntldj
°ÝÃD1
IF³¯­z¦¡ªº»yªk¨âºØ
1.   
If §PÂ_¦¡ then °õ¦æ³¯­z¦¡
2.   
If §PÂ_¦¡ then
          °õ¦æTRUE³¯­z¦¡
       Else
          °õ¦æFalse³¯­z¦¡
End If
°ÝÃD2
³o¼Ë§A´N¤£¤Àpart_id
¥u¬O§â©Ò¦³modifyªº¸¹½X¥þ¼g¤J¦P¤@­ÓÀx¦s®æ¶Ü?
  1. Sub nn()
  2. With Workbooks.Open("D:\book2.xls") 'Àɮ׸ô®|¦Û¦æ­×§ï
  3. With .Sheets("b")
  4. For Each a In .Range(.[A1], .[A65536].End(xlUp))
  5.   If a = "modify" Then
  6.      If mystr = "" Then
  7.         mystr = "'" & Right(a.Offset(, 1), 3) & "'"
  8.         Else
  9.         mystr = mystr & ",'" & Right(a.Offset(, 1), 3) & "'"
  10.      End If
  11.   End If
  12. Next
  13. End With
  14. .Close
  15. End With
  16. With Sheets("a")
  17.    .Cells(3, 2) = "(" & mystr & ")"
  18. End With
  19. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁª©¥D§A¼ö¤ßªº¦^ÂÐ ¯uªº«Ü·PÁÂ

¦³°ÝÃD·Q½Ð±Ð ¤W­±³o¦Ucodeªºmystr ¤£¥Î©w¸q¹À?? ¤£¥Î¥Î¨ìDictionary¤F?³o¼Ë¼g¬O©w¸qmystr¬O¯x°}ªº·N«ä¹À?

§ÚÀ³¸Ó¬O¤£¤Àpart id §âope_no¼g¶i¥hÀx¦s®æ¸Ì¨S¿ù
¦ý¬Opart_id ¬O­n¤ÀÀx¦s®æªº ¦p¤U­± y z ³o¼Ë
part_id                   y                      z            
                       ('356','456','556')      

§Ú·|¦n¦n§V¤Oªº ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD