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

match ·j´M¤£¦P¤u§@ªí¥i¥H«ü©wÅܼƶÜ?

¦^´_ 8# ikboy


    ÁÙ¦³¤@­Ó°ÝÃD¬O

¥´Application.WorkSheetFunction.Match...¸ò¥´Application.Match...¦³¤°»ò¤£¤@¼Ë?

TOP

²¼gªº±`¼Æ¤Gºû°}¦C, §Y = Application.Transpose(Application.Transpose(Array(Array("¼Æ­È1","°}¦C1"), Array("¼Æ­È2","°}¦C2"), Array("¼Æ­È3","°}¦C3"))))

TOP

¥´Application.WorkSheetFunction.Match...¸ò¥´Application.Match...¦³¤°»ò¤£¤@¼Ë?
¤S¬O²¼g

TOP

¦^´_ 12# ikboy


    ¤Ñ°Ú,«Ü·PÁ§Aªº¦^µª~!

TOP

¦^´_ 11# EGBT

¦^´_ 13# ikboy

°Ñ¦Ò³o¸Ì
http://forum.twbts.com/thread-10339-1-1.html
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦hÁÂGBKEEª©¥D«ü±Ð, À´¤F¡C

TOP

¦^´_ 15# GBKEE


    GBKEE¤j¤j±z¦n

§ÚªºMATCH°ÝÃD¦p¤U..(ÁöµM«Ü¦h¤j¤j³£¦³¦^µª¦ýÁÙ¬O¤£¤Ó¯à°÷Åý§Ú²z¸Ñ><)

°²³]x1¬°1~n­Ósheet (¦ý¤£ª¾¹D¦³´X­Ósheet),¨C­Ósheet¸Ì­±³£¦³¤£³W«h¼Æ­È,³o¸Ì´N¬O©ñ¦bMatchªº¼Æ­È

°²³]y1¬°1~n­Ósheet (¦ý¤£ª¾¹D¦³´X­Ósheet),¨C­Ósheet¸Ì­±³£¦³¤£³W«hªº°}¦C¼Æ,³o¸Ì´N¬O©ñ¦bMatchªº°}¦C

§Ú¥Î²ÊÁWªº¤èªk¥h­pºâ¦³´X­Óx1©M¸Ì­±ªº¼Æ­È¥H¤Î­pºây1©M¸Ì­±ªº°}¦C¼Æ

³o¼Ë¤£ª¾¹DMatch...ªþ¤WÀɮצn¹³¤]»¡¤£¤Ó©ú¥Õ

TOP

¦^´_ 17# EGBT
¸Õ¸Õ¬Ý §AªºªþÀɵ{¦¡½X
  1. Option Explicit
  2. Sub Ex()
  3.     Dim ¼Æ­Èªí As Variant, °}¦Cªí As Variant, E As Variant, EE As Variant
  4.     Dim Ar(), Ar1(1 To 2), Ar2(), i As Integer, M As Variant
  5.     '°²³]x1¬°1~n­Ósheet (¦ý¤£ª¾¹D¦³´X­Ósheet),¥Ñ¤U­±µ{¦¡½X¨ú±o
  6.     For Each E In Sheets
  7.         If E.Name Like "¼Æ­È*" Then ¼Æ­Èªí = ¼Æ­Èªí & "," & E.Name '¦r¦ê:¶°¦X¼Æ­Èªí
  8.         If E.Name Like "°}¦C*" Then °}¦Cªí = °}¦Cªí & "," & E.Name '¦r¦ê:¶°¦X°}¦Cªí
  9.     Next
  10.     '*******************************************
  11.     ¼Æ­Èªí = Split(Mid(¼Æ­Èªí, 2), ",")   '°}¦C: ¦r¦ê¥H","¤À³Î¬°°}¦C
  12.     °}¦Cªí = Split(Mid(°}¦Cªí, 2), ",")
  13.     ReDim Ar2(0)                           '­«¸m°}¦C
  14.     For Each E In Sheets(¼Æ­Èªí)           '¤u§@ªí°}¦C(¼Æ­Èªí)
  15.         '¨C¤@¼Æ­Èªí
  16.         Ar = E.Range("A:A").SpecialCells(xlCellTypeConstants).Value '¼Æ­È¸ê®Æ¸m¤J°}¦C
  17.         Ar = Application.WorksheetFunction.Transpose(Ar)            '°}¦C:¤Gºû(¾î)Âà¤@ºû(ª½)
  18.         For Each EE In Sheets(°}¦Cªí)                               '¤u§@ªí°}¦C(°}¦Cªí)
  19.             '¨C¤@°}¦Cªí
  20.             For i = 1 To UBound(Ar)                                 '¼Æ­È¸ê®Æ°}¦C
  21.                 M = Application.Match(Ar(i), EE.Range("A:A"), 0)    '¨C¤@°}¦Cªí¤¤Match¼Æ­È
  22.                 Ar1(1) = E.Name & " -" & Ar(i)
  23.                 If IsError(M) Then
  24.                     Ar1(2) = EE.Name & " - §ä¤£¨ì"
  25.                 Else
  26.                     Ar1(2) = EE.Name & " -A" & M
  27.                 End If
  28.                 Ar2(UBound(Ar2)) = Ar1
  29.                 ReDim Preserve Ar2(UBound(Ar2) + 1)    '°}¦C­«¸m¤¸¯À¯Á¤Þ­È,Preserve(«O¯d­ì¦³¤¸¯À)
  30.             Next
  31.         Next
  32.     Next
  33.     ReDim Preserve Ar2(UBound(Ar2) - 1) '°}¦CÂà¸m®É¦p¤l¤¸¯À¦³¬°°}¦C,¨ä¥L¤l¤¸¯À¶·¬°¬Û¦P¤j¤pªº°}¦C
  34.     Sheets("µ²ªG").Range("A1").Resize(UBound(Ar2) - 1, 2) = Application.Transpose(Application.Transpose(Ar2))
  35. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 18# GBKEE


    ¤Ñ°Ú..¯«¤H¤@ªT!!!

¤p§Ì¡@Orz...

²`«ä¤¤!!

TOP

¦^´_ 18# GBKEE


    §Ú·Q­n½Ð°Ý¤j¤j,
  1. Dim ¼Æ­Èªí As Variant, °}¦Cªí As Variant, E As Variant, EE As Variant
  2.     Dim Ar(), Ar1(1 To 2), Ar2(), i As Integer, M As Variant
  3.     '°²³]x1¬°1~n­Ósheet (¦ý¤£ª¾¹D¦³´X­Ósheet),¥Ñ¤U­±µ{¦¡½X¨ú±o
  4.     For Each E In Sheets
  5.         If E.Name Like "¼Æ­È*" Then ¼Æ­Èªí = ¼Æ­Èªí & "," & E.Name '¦r¦ê:¶°¦X¼Æ­Èªí
  6.         If E.Name Like "°}¦C*" Then °}¦Cªí = °}¦Cªí & "," & E.Name '¦r¦ê:¶°¦X°}¦Cªí
  7.     Next
  8.     '*******************************************
  9.     ¼Æ­Èªí = Split(Mid(¼Æ­Èªí, 2), ",")   '°}¦C: ¦r¦ê¥H","¤À³Î¬°°}¦C
  10.     °}¦Cªí = Split(Mid(°}¦Cªí, 2), ",")
  11.     ReDim Ar2(0)                           '­«¸m°}¦C
  12.     For Each E In Sheets(¼Æ­Èªí)           '¤u§@ªí°}¦C(¼Æ­Èªí)
½Æ»s¥N½X
¤W­±ªº¡uDim ¼Æ­Èªí As Variant¡v¥L¤£¬O¥u¬O±N¡u¼Æ­Èªí¡v«Å§i¦¨Variant

¬°¤°»ò¦b¤U¤è¡uIf E.Name Like "¼Æ­È*" Then ¼Æ­Èªí = ¼Æ­Èªí & "," & E.Name¡v¡A³o·Pı¦³ÂI¹³§â¬Û¦ü¡u¼Æ­È¡vªºSheet©ñ¤J¨ì¼Æ­Èªí¸Ì­±¡A¤£¬O°}¦C¤]¥i¥H³o»ò°µ¶Ü?

ÁÙ¦³¤U¤èªº¡uFor Each E In Sheets(¼Æ­Èªí)¡v±N¼Æ­Èªí©ñ¨ì¡uE¡v¸Ì­±..¼Æ­Èªí¥¦¤£¬O¥u¬O´¶³qªºÅܼƶÜ? «ç»ò¥i¥H§â¥¦Åܦ¨¹³¬O°}¦C©O?

§Ú¬Oı±o...Ãþ¦üSheets(¼Æ­È(S)) <- ¬O©ñ¡u¼Æ­È(S)¡v°}¦C¡A±N°}¦C©ñ¨ìE...

³Â·Ð¤j¤j¶}¥Ü

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD