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

EGBT

¦^´_ 26# GBKEE


    §ì¨ú¼Æ­È

§Ú¬Oª½±µ§ï

EE.Activate¡A¿E¬¡Sheet¥h§ì¨ú¡A¤£ª¾¹DGBKEE¤j¤j¬O§_¦³§ó¬¡ªº¤èªk©O?

GBKEE

°²³] "¼Æ­È1" ¦b "°}¦C1" °µMatch
¤ñ¹ï¥X¨Ó¬O¦n´X¦C,¨º§Ú­n«ç»ò§ì¥X¤ñ¹ï«áªº¦C¼Æ¡A¦bBÄ檺¤º®e?

½Ð°Ñ¦Ò ²Ä18¼Óªºµ{¦¡½X

§Ú¬Oª½±µ§ï
EE.Activate¡A¿E¬¡Sheet¥h§ì¨ú¡A
EGBT µoªí©ó 2015/9/19 18:50

¤£»Ý EE.Activate,EE.Select ²¾°Ê¬¡­¶Ã¯ªº¤u§@ªí

1. With EE
   
2.    .Range("A1")="XXX"
   
3. End With
   
4.   

½Æ»s¥N½X

citizen0923

¦p¥DÃD:match ·j´M¤£¦P¤u§@ªí¥i¥H«ü©wÅܼƶÜ?
°²³]~
¤u§@ªíAAA¸Ì­±AÄ榳
AA001
AA002

¤u§@ªíBBB¸Ì­±AÄ榳
BB001
BB002

¤u§@ªíCCC¸Ì­±·Q¨Ï¥ÎMATCH
A1Ä欰AAA
A2Ä欰BBB
A3Ä欰·j´M­È(°²³]¬°AA001)
A4¬°MATCHªºµ²ªG

°õ¦æCells(4, 1) = [MATCH(A3,AAA!A1:A20,0)]
¥i¸Õ¥X AA001 ¦b AAAªí¤¤ªº²Ä1­Ó¦ì¸m¡C
­Y·Q§â·í¤¤ªºAAA!§ï¦¨Åܼƪº¤è¦¡¡A½Ð°Ý¸Ó¦p¦ó­×§ï?

¨Ò¦p§ï¦¨
Cells(4, 1) = [MATCH(A3,Worksheets(Cells(1, 1)A1:A20,0)]
´N·|¥X²{¿ù»~¤F~ ¦³ÂI·Ð´o
ÁÙ±æ«e½ú«üÂI°g¬z¡A¦³³Ò¶O¤ß¤F¡AÁÂÁÂ

demo match.zip (16.2 KB)

¤U¸ü¦¸¼Æ: 1

2015-9-22 23:52

GBKEE

¦^´_ 1# citizen0923
°Ñ¦Ò³o¸Ì
    http://forum.twbts.com/thread-14979-1-1.html
   
http://forum.twbts.com/thread-2811-1-1.html

EGBT

¦^´_ 32# GBKEE


    WoW...Cool~

¦ý¬O§ÚÁÙ¦³¤@­Ó°ÝÃD...´N¬O §Ú¬O¤ñ¹ï¤é´Á

¤ñ¹ïªº¼Æ­È¬°
2015/1/1

¤ñ¹ïªº°}¦C¬°
2014/12/31
2015/1/2
2015/1/3
...
..
.

Match«á­±³]©w 1 §ä¤p©ó ·|§ä¨ì 2014/12/13

¦ý¬O§Ú³]©w -1 §ä¤j©ó ¥L¥X²{ "·¸¦ì" ¦Ó¤£·|§ä¨ì 2015/1/2 ...

°ÝÃD¥X¦b­þ¸Ì?

GBKEE

¦^´_ 33# EGBT

1. Option Explicit
   
2. Sub Ex()
   
3.    ' Dim I As Integer, AR()  '³o¼Ë·|·¸¦ì
   
4.     'Integer ÅܼƫY¥H½d³ò¬° -32,768 ¨ì 32,767 ¤§ 16 ¦ì¤¸ (2 ­Ó¦ì¤¸²Õ) ¼Æ¦rªº§Î¦¡Àx¦s
   
5.     Dim I As Single, AR()
   
6.     'Single (³æºë«×¯BÂI¼Æ) ÅܼƫY¥H IEEE 32 ¦ì¤¸ (4 ­Ó¦ì¤¸²Õ) ¯BÂI¼Æ¦rªº§Î¦¡Àx¦s¡A
   
7.     '¥¦ªº½d³ò¦b­t¼Æªº®É­Ô¬O±q -3.402823E38 ¨ì -1.401298E-45¡A
   
8.     '¦Ó¦b¥¿¼Æªº®É­Ô¬O±q 1.401298E-45 ¨ì 3.402823E38¡CSingle ªº«¬ºA«Å§i¦r¤¸¬° !¡C
   
9.    
   
10.     ReDim AR(1 To Rows.Count)
    
11.     AR(Rows.Count) = #1/1/2015#
    
12.     I = Application.Match(#2/1/2015#, AR, -1)
    
13.     MsgBox I
    
14. 
    
15. End Sub

½Æ»s¥N½X

EGBT

¦^´_ 34# GBKEE


    ¦A½Ð°ÝGBKEE¤j¤j

Integer½d³ò¬° -32,768 ¨ì 32,767 ¤§ 16 ¦ì¤¸·|·¸¦ì,

¦ÓSingle­t¼Æªº®É­Ô¬O±q -3.402823E38 ¨ì -1.401298E-45

¥¿¼Æªº®É­Ô¬O±q 1.401298E-45 ¨ì 3.402823E38¡A¬°¤°»òSingle´N¤£·|·¸¦ì©O¡H§ÚÁÙ¬O¤£¤ÓÀ´³o­Ó¹D²z...

ReDim AR(1 To Rows.Count)
    AR(Rows.Count) = #1/1/2015# ' <- ¤£¯à¼g¦º¡AMatch´N¬O-1¡A§ì¤£¨ìªº®É­Ô¤~·|§ä¤j©ó¦Û¤vªº¤é´Á
    I = Application.Match(#2/1/2015#, AR, -1)
    MsgBox I

«¢~«Ü·PÁÂGBKEE¤j¤j³o»ò­@¤ßªº¦^Âиѵª°Ú~

EGBT

¦^´_ 34# GBKEE


    ¨Ò¦pªþÀÉ~

³o¼Ë¤l¦n¹³¤]¬O #N/A¡B¿ù»~2048 ...«¢....¥i¯à§ÚÁÙ¨S¿ìªk»â®©GBKEE¤j¤j©Ò»¡ªº

1. Sub test()
   
2. Dim i, y, x As Single
   
3. i = Application.Match("A1", Range("C1:C14"), -1)
   
4. Range("B1") = i
   
5. y = Application.Match("A2", Range("C1:C14"), -1)
   
6. Range("B2") = y
   
7. x = Application.Match("A3", Range("C1:C14"), -1)
   
8. Range("B3") = x
   
9. End Sub

½Æ»s¥N½X

citizen0923

G¤j±z¦n~³o¨â½g§Ú³£¦³¬Ý¹L¤F³á~ ¤£¹L¯à²z¸Ñ¦³­­¡A
¤j·§ª¾¹D[]¤¤¤£±µ¨üÅܼƪº¦r¦ê¡A©Ò¥H§âµ{¦¡½X§ï¦¨
Private Sub CommandButton1_Click()
Cells(4, 1) = Application.Match(A3, Sheets("AAA").Range("A1:A10"), 0)
End Sub
'¥Ø¼Ð¬O³z¹L¥Ø«e¤u§@ªíCCCªºA3Äæ¸ê®Æ(AA001)¡A¥hmatch¤u§@ªíAAA¤¤A1~A10²Å¦XAA001ªº¦ì¸m«á¡A¼g¦b¤u§@ªíCCCªºCells(4, 1)¦ì¸m
¦p¦¹ÁÙ¬O·|§ä¤£¨ì¥Ø¼Ð³á~ ¯àÀ°¦£«üÂI¶Ü~~ ¤£¦n·N«ä¤F

GBKEE

¦^´_ 3# citizen0923

1. Private Sub CommandButton1_Click()
   
2.     'Cells(4, 1) = Application.Match(A3, Sheets("AAA").Range("A1:A10"), 0)
   
3.     Cells(4, 1) = Application.Match([A3], Sheets("AAA").Range("A1:A10"), 0)
   
4.     Cells(4, 1) = Application.Match(Range("A3"), Sheets("AAA").Range("A1:A10"), 0)
   
5. End Sub

½Æ»s¥N½X