- ©«¤l
- 4
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 5
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 10
- µù¥U®É¶¡
- 2015-6-30
- ³Ì«áµn¿ý
- 2015-7-8
|
[µ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- Private Sub CommandButton4_Click() '¿é¤J¤u¥d¸¹½X
- Dim a As String, cardnumber As String
- Application.ScreenUpdating = False
- cardnumber = InputBox("½Ð¿é¤J¤u¥d¸¹½X(«Øij¨Ï¥Î±ø½X¾¹)")
- i = 9
- Sheets("¸ê®Æ®w").Activate
- a = Application.WorksheetFunction.Match(CDbl(cardnumber), Sheets("¸ê®Æ®w").[B:B], 0) '³]©w¸ê®Æ®w¸ÌªºBÄæ·j´Mµ²ªG¬°a
- If a = "0" Then
- MsgBox "¥¼·j´M¨ì±z©Ò¿é¤Jªº¤u¥d¸¹½X¡A½Ð½T»{¸ê®Æ¨Ó·½µL»~¡C"
- Sheets("µn¿ý").Select
- Exit Sub
- Else
- Sheets("µn¿ý").Range("A2") = cardnumber
- firstAddress = Cells(a, 2).Address
-
- Do
- Sheets("¸ê®Æ®w").Select
- ActiveSheet.Range(ActiveSheet.Cells(a, 1), ActiveSheet.Cells(a, 62)).Select '¿ï¾Ü¨Ã½Æ»sÄæ¦ì
- Selection.Copy
-
- Sheets("µn¿ý").Select
- '¦pªG§P©wBÄæCÄæ¤ÎKÄæ³£¬°ªÅȪº¸Ü«h¶K¤W
- If (ActiveSheet.Cells(i, 2) = "" And ActiveSheet.Cells(i, 3) = "" And ActiveSheet.Cells(i, 6) = "") Then
- ActiveSheet.Cells(i, 2).Select
- Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
- Application.CutCopyMode = False
-
- Sheets("¸ê®Æ®w").Select
-
- 'a = a.Nextmatch()
- secondAddress = Cells(a, 2).Address '§PÂ_ADDRESS«á¿ï¨ú½Æ»sÄæ¦ì
- Else
- End If
- i = i + 1
-
- Loop While secondAddress <> firstAddress
- Sheets("µn¿ý").Select
-
- End If
- Range("K9") = "=G7"
- Range("K10") = "=H7"
- Range("K11") = "=I7"
- Range("K12") = "=J7"
- Range("K13") = "=K7"
- Range("K14") = "=L7"
- Range("K15") = "=M7"
- Range("K16") = "=N7"
- Range("K17") = "=O7"
- Range("K18") = "=P7"
- Application.ScreenUpdating = True
- End Sub
½Æ»s¥N½X
TEST.rar (78.67 KB)
|
|