GBKEE

¦^´_ 3# EGBT
¬O³o¼Ë¶Ü?

1. Option Explicit
   
2. Sub EX()
   
3.     Dim ¼Æ­È(), °}¦C(), e As Variant, M As Variant
   
4.     ¼Æ­È = Array(0, "a" , 100 , "bb", 88)
   
5.     °}¦C = Array(88, "cc", 0, "a", "bb")
   
6.     For Each e In ¼Æ­È
   
7.         M = Application.Match(e, °}¦C, 0)
   
8.         Debug.Print e, M '«ü¥O:À˵ø->§Y®É¹Bºâµøµ¡
   
9.     Next
   
10. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 11# EGBT

¦^´_ 13# ikboy

°Ñ¦Ò³o¸Ì
http://forum.twbts.com/thread-10339-1-1.html

GBKEE

¦^´_ 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

GBKEE

¦^´_ 20# EGBT

1. Sub Ex()
   
2.     Dim ¼Æ­Èªí As Variant, °}¦Cªí As Variant, E As Variant
   
3.     'Variant ¸ê®Æ«¬ºA¬O©Ò¦³¨S³Q©ú½T«Å§i¬°¨ä¥L«¬ºA,¥i¥H¬°»{¦óªº¸ê®Æ«¬ºA
   
4.     For Each E In Sheets
   
5.         If E.Name Like "¼Æ­È*" Then
   
6.             ¼Æ­Èªí = ¼Æ­Èªí & "," & E.Name '¦r¦ê:¶°¦X¼Æ­Èªí
   
7.            MsgBox VarType(¼Æ­Èªí) '8 (¦r¦ê )
   
8.         End If
   
9.     Next
   
10.     '*******************************************
    
11.     'Split : ¦r¦ê¥H "," (¥ô¦ó«ü©wªº¦r¦ê) ¤À³Î¦Ó¦¨¬°°}¦C
    
12.     ¼Æ­Èªí = Split(Mid(¼Æ­Èªí, 2), ",")
    
13.     '*******************************************
    
14.     MsgBox VarType(¼Æ­Èªí) '8192 (°}¦C) + 8 (¦r¦ê )
    
15.     For Each E In Array("¼Æ­È1", "¼Æ­È2", "¼Æ­È3")         '¤u§@ªí°}¦C(¼Æ­Èªí)
    
16.         MsgBox VarType(E)   '8 (¦r¦ê )
    
17.     Next
    
18.     'For Each E In Sheets(Array("¼Æ­È1", "¼Æ­È2", "¼Æ­È3"))         '¤u§@ªí°}¦C(¼Æ­Èªí)
    
19.     For Each E In Sheets(¼Æ­Èªí)           '¤u§@ªí°}¦C(¼Æ­Èªí)
    
20.         MsgBox VarType(E)   ' 9 ª«¥ó
    
21.     Next
    
22. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 25# EGBT

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim ¼Æ­Èªí As Variant, e As Variant
   
4.     ¼Æ­Èªí = "¼Æ­È¥Ò%A#¼Æ­È1%A#¼Æ­È2%A#¼Æ­È123456%A#¼Æ­Èabc"
   
5.     '*******************************************
   
6.     'Split : ¥H©Ò«ü©wªº¦r¦ê, ¤À³Î¦Ó¦¨¬°°}¦C
   
7.     ¼Æ­Èªí = Split(¼Æ­Èªí, "%A#")
   
8.     '*******************************************
   
9.     For Each e In ¼Æ­Èªí
   
10.         MsgBox e
    
11.     Next
    
12. End Sub

½Æ»s¥N½X

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

GBKEE

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

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

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

GBKEE

¦^´_ 38# EGBT
401298E-45 ¨ì 3.402823E38 ¬O¬ì¾Ç°O¸¹,½ÐGOOGLE ¬d¬Ý¤F¸Ñ
Dim i, y, x As Single
'i, y ¨S«ü©w³£¬O As Variant,V½Ð¦Û¦æ¬d¬Ý VBA ªº»¡©ú
i, y             µ{¦¡¥i¥H±µ¨ü¶Ç¦^¿ù»~­È,
x As Single ¥²¶·¬O¶Ç¦^¼Æ¦r,µ{¦¡¤£±µ¨ü¿ù»~­È.

1. Application.Match("A1", Range("C1:C14"), -1)

½Æ»s¥N½X

½Ð¬Ý40¼Óªºµ{¦¡½X