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

[µo°Ý] ¨D§U~~¥ÎVBA»s§@¤@­Ó¸ê®Æ´M§ä~¸ê®Æ·h²¾ªº¤èªk~~

[µo°Ý] ¨D§U~~¥ÎVBA»s§@¤@­Ó¸ê®Æ´M§ä~¸ê®Æ·h²¾ªº¤èªk~~

§Ú·Q­n¥ÎVBA¼g¤@­Ó   ¾ú¦~¦¨ÁZ    ªº¶×¤J¤u¨ã¡C
(1)­n±q95¦~¤U¾Ç´Á´Á¤¤¦Ò¤u§@ªí    ¶×¨ì«ü©wªºÀx¦s®æ¤º¡C
(2)¹³  ¼B­P²W  ¡F°²¦p§Ú¤£¤p¤ß¦b  ¾ú¦~¦¨ÁZ ¤º«Ø¤F¨â­Ó¼B­P²Wªº¦W¤l¡A«h¨âµ§³£­n¯à¶ñ¤J   ¼B­P²W¾ú¦~¦¨ÁZ¡C
(3)¦pªGµ{¦¡°õ¦æ§ä ¦¿²M©y ®É¡A­n¦³¤èªk  ¯à¦^¶Ç¥¦ªº¦Cªº­È  ¬°10 ¡C
(4)¥t¥~¤@­Ó«ö¶s¥\¯à¬O~~¯à§â©Ò¦³­«ÂЪº¦W¤l~Àx¦s®æÅܦ¨¬õ¦â©³¡C

(5)§Ú¥Ø«eªº¼gªk¬O¥Î¨â­Ófor°j°é§ä¡A
  (a) ¥ý¨ú¥X     95¦~¤U¾Ç´Á´Á¤¤¦Ò      A3¤º¤H¦W¤ý¤p©ú-
  (b)¦A¨ì¾ú¦~¦¨ÁZ¤º¥Îfor°j°é±qA3¡BA4¡BA5¡BA6¡K©¹¤U§ä¡A§ä¨ì¦A·h¸ê®Æ¡C
  (C)¦A¨ú¥X   95¦~¤U¾Ç´Á´Á¤¤¦Ò      A4¤º¤H¦W¾Gªü®S¡C
  (D)¦A¨ì¾ú¦~¦¨ÁZ¤º¥Îfor°j°é±qA3¡BA4¡BA5¡BA6¡K©¹¤U§ä¡A§ä¨ì¦A·h¸ê®Æ¡C
´N¬O¥H«e¾Ç®Õ¾Çªº¨º®M
¦ý·í¸ê®Æ«Ü¤j®É¡Aµoı¥¦­n¤@¨Ç®É¶¡¤~¶]§¹¡C
ı±o¨S®Ä²v
¬G¨D§U~~¦U¦ì¥ý¶i«ü¾É¦³µL¨ä¥L ¸û¦nªº~~~¤èªk¡A¥i¥H§ïµ½  ÁÂÁÂ~~~~·P®¦~~~~   ^^    ¡C

¥»©«³Ì«á¥Ñ register313 ©ó 2012-5-13 23:04 ½s¿è

¦^´_ 1# ji12345678

µo°Ý¶·ª¾:
1.µo°ÝªÌ½Ð¤W¶ÇEXCELÀ£ÁYÀÉ(¤p¾Ç¥Í¤]¥i¥H)
2.°t¦XEXCELÀɽЧâ¥\¯à»¡©ú²M·¡

À°§U¦Û¤v!¤]µ¹µªÃDªÌ¤@­Ó¤è«K!
  1. Sub xx()
  2. With Sheets("¾ú¦~¦¨ÁZ")
  3.   Set Rng = .Range("A3:A" & .[A65536].End(xlUp).Row)
  4.   Rng.Interior.ColorIndex = 0
  5.   For C = 2 To .[IV1].End(xlToLeft).Column
  6.     Sh = .Cells(1, C) & .Cells(2, C)
  7.     For S = 1 To Sheets.Count
  8.       If Sh = Sheets(S).Name Then
  9.       For Each A In Rng
  10.         X = Application.VLookup(A.Value, Sheets(Sh).[A3:B65536], 2, 0)
  11.         If Not IsError(X) Then A.Offset(0, C - 1).Value = X
  12.         If Application.CountIf(Rng, A) > 1 Then A.Interior.ColorIndex = 3
  13.       Next
  14.       End If
  15.     Next S
  16.   Next C
  17. End With
  18. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# register313
register313¤j¤j
¥i¥H³Â·Ð¼g¤@¤Uµù¸Ñ¶Ü??
¤p§Ì¤]¦³»Ý­nÃþ¦üªº¥\¯à
¦ý¬O¬Ý¤£¤ÓÀ´= =
·Ð½Ð©âªÅ.....

TOP

¦^´_ 3# toxin
  1. Sub xx()
  2. '³]©w¥H¤U¦b¾ú¦~¦¨ÁZ¤u§@ªí¤§¤U¤u§@
  3. With Sheets("¾ú¦~¦¨ÁZ")
  4.   '³]©w¾ú¦~¦¨ÁZ¤¤A3~AÄæ³Ì«á¤@¦C¬°Rng½d³ò
  5.   Set Rng = .Range("A3:A" & .[A65536].End(xlUp).Row)
  6.   '²M°£Rng½d³ò¤§©³¦â
  7.   Rng.Interior.ColorIndex = 0
  8.   '°j°é1:C=2~²Ä1¦C³Ì«á1Ä椧¦C¸¹(²Ä1¦C¦³­Èªº½d³ò)
  9.   For C = 2 To .[IV1].End(xlToLeft).Column
  10.     'Sh=B1&B2,C1&C2,D1&D2(³]©w¾Ç¦~«×¾Ç´Á¦Ò¦¸¦WºÙ)
  11.     Sh = .Cells(1, C) & .Cells(2, C)
  12.     '°j°é2:S=1~³Ì«á¤@±i¤u§@ªí¤§¼Æ¶q
  13.     For S = 1 To Sheets.Count
  14.       '­Y(¾Ç¦~«×¾Ç´Á¦Ò¦¸¦WºÙ)=(¤u§@ªí¦WºÙ)«h°õ¦æ°j°é3
  15.       If Sh = Sheets(S).Name Then
  16.       '°j°é3:A=A3~AÄæ³Ì«á¤@¦C¤§­È
  17.       For Each A In Rng
  18.         '¬dA(©m¦W)¦b¬Û¹ï(¾Ç¦~«×¾Ç´Á¦Ò¦¸)¤u§@ªíA3~A65536¤§¦ì¸m,§ä¨ì«á¶Ç¦^²Ä2Ä椧­È(¤À¼Æ)
  19.         X = Application.VLookup(A.Value, Sheets(Sh).[A3:B65536], 2, 0)
  20.         '­Y¶Ç¦^­È(¤À¼Æ)¤£¬O¿ù»~«hA(©m¦W)¦V¥k°¾²¾C-1Äæ«á¶ñ¤J¤À¼Æ
  21.         If Not IsError(X) Then A.Offset(0, C - 1).Value = X
  22.         '­YA(©m¦W)¦bA3~AÄæ³Ì«á¤@¦C­«ÂÐ¥X²{,«h¶ñ¤J¬õ¦â©³¦â
  23.         If Application.CountIf(Rng, A) > 1 Then A.Interior.ColorIndex = 3
  24.       Next
  25.       End If
  26.     Next S
  27.   Next C
  28. End With
  29. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# register313

·PÁÂregister313¤j¤j©âªÅ¼gµù¸Ñ
¤p§Ì³o¨â¤Ñ¬ã¨s¤@¤U¦³°ÝÃD¦A³Â·Ð¤j¤j¤F
·P®¦

TOP

ÁÂÁ¦U¦ì¥ý¶i¼ö¤ß«ü±Ð¡A¨Ó¨ì³oùسo­Ó½×ôίuªº¾Ç¨ì¦n¦h¡A·PÁÂ~~~¡C

TOP

ÂǵۦU¦ì´£°Ý¤Î°ª¤âªº¤£§[¸Ñµª¡AÅý¦³¤ß¾Ç²ßªº¯à´£¤É¦Û¤vEXCELªº¥\¤O¡A«Ü°ª¿³¯à¨Ó³o½×¾Â^^

TOP

·PÁ¤À¨É.
«ØijÅܼƦb¨Ï¥Î¤§«e, ¶i¦æ«Å§i.

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD