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

[µo°Ý] Ãö©óWorksheetFunction.matchªº¨Ï¥Î¤èªk?

[µo°Ý] Ãö©óWorksheetFunction.matchªº¨Ï¥Î¤èªk?

¤£¦n·N«ä ¦U¦ì«e½ú ³Ìªñ¤p§Ì¦b¤u§@¤W­è¦n¦³¨Ï¥Î¨ìEXCELªºVBA
¦ý¬O«ê¦n¸I¨ì¤@¨Ç°ÝÃD¹ê¦b·d¤£À´ ©Ò¥H¤W¨Óµo°Ý½Ð¨D«üÂI!!

1.¥»¨Ó¬O¨Ï¥Î Find ¤èªk¨Ó´M§ä¬Û²Åªº¸ê®Æ¨Ã¥Î FindNext ¨Ó´M§ä¤U¤@µ§¡A¦ýµo²{¸ê®Æ¶q¤@¤j¤§«á¹q¸£ªº°õ¦æ³t«×¹ê¦bºC¨ì¦³ÂI¸Ø±i(2000µ§¸ê®Æ»Ý­n20¬í¥ª¥k)¡A¬G§ï¼g¤FMatchªº¤èªk¡C
2.Match¤èªk¤ä´©ªº¸ê®Æ«¬ºA¬O­þºØ©O?¦]¬°¤p§Ì¨Ï¥Îstring«¬ºA¨Ó°õ¦æ(¤]¹Á¸Õ¹LVariant¤@¼Ë·|¿ù)¡Aµo²{³¡¤Àªº¸ê®Æ¶×¤J¬O¦æ±o³qªº(EX:21040523007)³oµ§¥i¥H¡A¦ý¦³³¡¤À¸ê®Æ«o¸õ¥X¿ù»~1004 µLªk¨ú±oÃþ§OworksheetFunction ªº Match ÄÝ©Ê(EX:21040508010)³oµ§¡A¦³ÂIºÃ´b¡A¦]¬°³£¬O¥Î¦P¤@§PÂ_¤è¦¡¡A«ç»ò·|¦³¦hºØ¤£¦Pªºµ²ªG¡C
3.Matchªº´M§ä¤U¤@µ§ªº»yªk¤p§Ì¥hMSDN¬Ý¹L¡A¥i¬O¤£¬O«Ü¤F¸Ñ¡A¤W­±¥u¼g¤F "match = match.NextMatch()  " ¥i¬O§Ú§â¥¦ÅܼÆÂà´«±a¶i¥h«á¡A¸õ¥X¿ù»~ ¤£¥¿½Tªº©w¦ì¶µ(qualifier)¡C

¥H¤W¤T­Ó°ÝÃD¯uªº«ÜÀY¯k¡A¦b¦¹ªþ¤WÀɮתþ¥ó¥H¤Î¥N½X³Â·Ð¦U¦ì«e½ú«ü¤Þ¤@¤U¡A·P¿E¤£ºÉ¡C

¡°CODEªº¦ì¸m¦b"µn¿ý"¦¹¤u§@ªí¤ºªº"¿é¤J¸¹½X"ªº¦ì¸m
  1. Private Sub CommandButton4_Click() '¿é¤J¤u¥d¸¹½X

  2. Dim a As String, cardnumber As String

  3. Application.ScreenUpdating = False

  4. cardnumber = InputBox("½Ð¿é¤J¤u¥d¸¹½X(«Øij¨Ï¥Î±ø½X¾¹)")
  5. i = 9

  6. Sheets("¸ê®Æ®w").Activate

  7. a = Application.WorksheetFunction.Match(CDbl(cardnumber), Sheets("¸ê®Æ®w").[B:B], 0) '³]©w¸ê®Æ®w¸ÌªºBÄæ·j´Mµ²ªG¬°a
  8. If a = "0" Then
  9.     MsgBox "¥¼·j´M¨ì±z©Ò¿é¤Jªº¤u¥d¸¹½X¡A½Ð½T»{¸ê®Æ¨Ó·½µL»~¡C"
  10.     Sheets("µn¿ý").Select
  11.     Exit Sub
  12. Else
  13.     Sheets("µn¿ý").Range("A2") = cardnumber
  14.     firstAddress = Cells(a, 2).Address
  15.                   
  16.     Do
  17.         Sheets("¸ê®Æ®w").Select
  18.         ActiveSheet.Range(ActiveSheet.Cells(a, 1), ActiveSheet.Cells(a, 62)).Select '¿ï¾Ü¨Ã½Æ»sÄæ¦ì
  19.         Selection.Copy
  20.    
  21.         Sheets("µn¿ý").Select
  22.             '¦pªG§P©wBÄæCÄæ¤ÎKÄæ³£¬°ªÅ­Èªº¸Ü«h¶K¤W
  23.             If (ActiveSheet.Cells(i, 2) = "" And ActiveSheet.Cells(i, 3) = "" And ActiveSheet.Cells(i, 6) = "") Then
  24.             ActiveSheet.Cells(i, 2).Select
  25.             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  26.             Application.CutCopyMode = False
  27.         
  28.             Sheets("¸ê®Æ®w").Select
  29.         
  30.             'a = a.Nextmatch()
  31.             secondAddress = Cells(a, 2).Address '§PÂ_ADDRESS«á¿ï¨ú½Æ»sÄæ¦ì
  32.             Else
  33.             End If
  34.         i = i + 1
  35.            
  36.     Loop While secondAddress <> firstAddress
  37.     Sheets("µn¿ý").Select
  38.    
  39. End If

  40. Range("K9") = "=G7"
  41. Range("K10") = "=H7"
  42. Range("K11") = "=I7"
  43. Range("K12") = "=J7"
  44. Range("K13") = "=K7"
  45. Range("K14") = "=L7"
  46. Range("K15") = "=M7"
  47. Range("K16") = "=N7"
  48. Range("K17") = "=O7"
  49. Range("K18") = "=P7"


  50. Application.ScreenUpdating = True
  51. End Sub
½Æ»s¥N½X
TEST.rar (78.67 KB)

¦^´_ 1# l020330320

·j´M³t«×­n§Ö¥i¥H§Q¥Î°}¦C¨Ó§ä³t«×·|§Ö«Ü¦hªº¡I¤U­±¥N½Xµ¹§A°Ñ¦Ò¡I
  1. Private Sub CommandButton4_Click() '¿é¤J¤u¥d¸¹½X
  2. Dim a As String, cardnumber As String
  3. Dim arr2()
  4. Application.ScreenUpdating = False
  5. cardnumber = InputBox("½Ð¿é¤J¤u¥d¸¹½X(«Øij¨Ï¥Î±ø½X¾¹)")
  6. i = 9
  7. x = 1
  8. arr = Sheets("¸ê®Æ®w").Range("a2:bj" & Sheets("¸ê®Æ®w").Cells(Rows.Count, 2).End(xlUp).Row)

  9. For j = 1 To UBound(arr, 1)
  10.     If arr(j, 2) = cardnumber Then
  11.         ReDim Preserve arr2(1 To 42, 1 To x)
  12.         arr1 = Application.Transpose(Application.Index(arr, j, 0))
  13.         For s = 1 To 42
  14.             arr2(s, x) = Application.Transpose(arr1(s, 1))
  15.         Next
  16.         x = x + 1
  17.     End If
  18. Next
  19. [a2] = cardnumber
  20. [b9].Resize(UBound(arr2, 2), UBound(arr2, 1)) = Application.Transpose(arr2)
  21. 'Sheets("¸ê®Æ®w").Activate

  22. 'a = Application.WorksheetFunction.Match(CDbl(cardnumber), Sheets("¸ê®Æ®w").[B:B], 0) '³]©w¸ê®Æ®w¸ÌªºBÄæ·j´Mµ²ªG¬°a
  23. 'If a = "0" Then
  24. '    MsgBox "¥¼·j´M¨ì±z©Ò¿é¤Jªº¤u¥d¸¹½X¡A½Ð½T»{¸ê®Æ¨Ó·½µL»~¡C"
  25. '    Sheets("µn¿ý").Select
  26. '    Exit Sub
  27. 'Else
  28. '    Sheets("µn¿ý").Range("A2") = cardnumber
  29. '    firstAddress = Cells(a, 2).Address
  30. '
  31. '    Do
  32. '        Sheets("¸ê®Æ®w").Select
  33. '        ActiveSheet.Range(ActiveSheet.Cells(a, 1), ActiveSheet.Cells(a, 62)).Select '¿ï¾Ü¨Ã½Æ»sÄæ¦ì
  34. '        Selection.Copy
  35. '
  36. '        Sheets("µn¿ý").Select
  37. '            '¦pªG§P©wBÄæCÄæ¤ÎKÄæ³£¬°ªÅ­Èªº¸Ü«h¶K¤W
  38. '            If (ActiveSheet.Cells(i, 2) = "" And ActiveSheet.Cells(i, 3) = "" And ActiveSheet.Cells(i, 6) = "") Then
  39. '            ActiveSheet.Cells(i, 2).Select
  40. '            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  41. '            Application.CutCopyMode = False
  42. '
  43. '            Sheets("¸ê®Æ®w").Select
  44. '
  45. '            'a = a.Nextmatch()
  46. '            secondAddress = Cells(a, 2).Address '§PÂ_ADDRESS«á¿ï¨ú½Æ»sÄæ¦ì
  47. '            Else
  48. '            End If
  49. '        i = i + 1
  50. '
  51. '    Loop While secondAddress <> firstAddress
  52. '    Sheets("µn¿ý").Select
  53. '
  54. 'End If
  55. Range("K9") = "=G7"
  56. Range("K10") = "=H7"
  57. Range("K11") = "=I7"
  58. Range("K12") = "=J7"
  59. Range("K13") = "=K7"
  60. Range("K14") = "=L7"
  61. Range("K15") = "=M7"
  62. Range("K16") = "=N7"
  63. Range("K17") = "=O7"
  64. Range("K18") = "=P7"
  65. Application.ScreenUpdating = True
  66. End Sub
½Æ»s¥N½X

TOP

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

TOP

¦^´_ 2# lpk187

¦nªº¡AÁÂÁÂL¤jªº´£ÂI¡A§Ú¤]¥¿¦b·Q¦pªG¨S¿ìªk·d©w¡A¦A´«­Ó¤è¦¡¼g¬Ý¬Ý!!

TOP

¦^´_ 3# GBKEE

G¤jÁÂÁÂÅo¡A­ì¨Ó±`±`µo¥Í¿ù»~§ä¤£¨ìmatchªº­È?¥i¬O©ú©úªí³æ¸Ì­±¦³¨º­Ó­Èªü...«ç»òÁÙ¬O·|§ä¤£¨ì©O?

TOP

¦^´_ 5# l020330320




¦Û¤v§ï¤@¤U Àx¦s®æ ¬O¤å¦r®æ¦¡ , CDbl ®æ¦¡¬° Double ¼Æ¦r
   
  1. Sheets("¸ê®Æ®w").Activate
  2.         'CDbl(cardnumber)
  3. a = Application.Match(cardnumber, Sheets("¸ê®Æ®w").[B:B], 0) '³]©w¸ê®Æ®w¸ÌªºBÄæ·j´Mµ²ªG¬°a
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# GBKEE

·PÁ¡A¦]¬°office¨S¦³Åã¥Üºñ¦â¼ÐÅÒ´£¿ô¡A©~µM§Ñ°O¤F³Ì­ì©lÀx¦s®æ®æ¦¡ªº°ÝÃD¡CÃø©Ç°ÝÃD¹é¹é....·PÁ«ü¾É~!!

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD