[µo°Ý] ¦p¦ó³]©w

acdx

§Ú­n­­¨îTextBox1¤ÎTextBox2©Ò¿é¤Jªº¼Æ­È¥²¶·¬°"100, 125, 160, 200, 250, 315, 400, 500,630, 800, 1000, 1250, 1600, 2000, 2500, 3150,4000, 5000, 6300, 8000, 10000, 12500, 16000, 20000",­Y¤£¬O«h¸õ¥Xĵ§iµøµ¡ ³o¸Ó«ç»ò¼g?
¥i§_¨Ï¥Î¯x°}
OB = Array(100, 125, 160, 200, 250, 315, 400, 500, _
                630, 800, 1000, 1250, 1600, 2000, 2500, 3150, _
                4000, 5000, 6300, 8000, 10000, 12500, 16000, 20000)
µM«á¸Ó«ç»ò¼g¤ñ¸û¦n?

stillfish00

¦^´_ 9# ML089
¦^´_ 10# GBKEE
©ú¥Õ¤F¡A¤S®³ Lookup ©M Index ¤ñ¸û¤@¤U¡A¤]¬OÃþ¦ü±¡§Î
    v1 = Application.Lookup(0, Array(1, 2, 3))
    v2 = Application.Index(Array(1, 2, 3), 4)
¬Ù²¤ WorkSheetFunction => ¥u·|¨Ï¨ç¼Æ¶Ç¦^¿ù»~­È
¦³¥[ WorkSheetFunction => ³y¦¨°õ¦æ¶¥¬q¿ù»~
¤]ÁÂÁ´£¨ÑÅܳq¤èªk°Ñ¦Ò¡C

GBKEE

¦^´_ 6# acdx

1. Private Sub UserForm_Initialize()   'ExportResult(ªí³æ¼Ò²Õ)
   
2. Dim Ob()   '<-³oDim Ob ÅÜ¼Æ ¥u¥i¥H¦b UserForm_Initializeµ{§Ç¤¤¨Ï¥Î
   
3. Ob = Array(100, 125, 160, 200, 250, 315, 400, 500, _
   
4.             630, 800, 1000, 1250, 1600, 2000, 2500, 3150, _
   
5.             4000, 5000, 6300, 8000, 10000, 12500, 16000, 20000)
   
6. End Sub

½Æ»s¥N½X

1. Option Explicit         'ExportResult(ªí³æ¼Ò²Õ)
   
2. Dim Ob(), Msg As Boolean  ' ³o¨â¦æµ{¦¡½X ½Ð©ñ¦b ExportResult(ªí³æ¼Ò²Õ)ªº³Ì¤WºÝ
   
3. (¦p¦¹³oDim Ob ÅܼÆ,ExportResult(ªí³æ¼Ò²Õ)©Ò¦³µ{¦¡¥i¥G¥sªº)
   
4. Private Sub CommandButton2_Click()
   
5. Unload Me
   
6. ALtoolbox.Show
   
7. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 2# acdx

1. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   
2. ob = Array(100, 125, 160, 200, 250, 315, 400, 500, 630, 800, 1000, 1250, 1600, 2000, 2500, 3150, 4000, 5000, 6300, 8000, 10000, 12500, 16000, 20000)
   
3. Set dic = CreateObject("Scripting.Dictionary")
   
4. For Each ky In ob
   
5.    dic.Add ky, ky
   
6. Next
   
7. If dic.exists(Val(TextBox1)) = False Then MsgBox "¿é¤J­È¿ù»~"
   
8. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 9# stillfish00
Match ¤£¬O¯S¨Ò, ¨Ï¥ÎApplication.WorksheetFunction¨ä¥L¨ç¼Æ¶Ç¦^¿ù»~­È,VBA ·|¦³²£¥Í¿ù»~­È.

1. Option Explicit
   
2. Sub TEST()
   
3.     Dim A As Variant
   
4.     A = Application.Match(999, Array(1, 2, 3), 0)
   
5.     If IsError(A) Then MsgBox "Not found"
   
6.     '¥i¥Î [ ­pºâ¬¡­¶Ã¯ªº¦WºÙ©Î¨ç¼Æ ]
   
7.     A = [MATCH(999,{1, 2, 3},0)]  '[ ³o¤£¥i¥ÎÅܼÆ(­pºâ¬¡­¶Ã¯ªº¦WºÙ©Î¨ç¼Æ) ]
   
8.     If IsError(A) Then MsgBox "Not found"
   
9.     ' **** Åܳq¤è¦¡******************
   
10.     Names.Add "AAA", Array(1, 5, 100, 3)
    
11.     Names.Add "B", 100
    
12.     If IsError([MATCH(B,AAA,0)]) Then
    
13.         MsgBox "Not found"
    
14.     Else
    
15.         MsgBox [MATCH(B,AAA,0)]
    
16.     End If
    
17. End Sub

½Æ»s¥N½X

ML089

¦^´_ 9# stillfish00

ºô¸ô¤W§äªº½d¨Ò´£¨Ñ°Ñ¦Ò¡A
¬Ý¨Ó application.XXX »P WorksheetFunction.XXX©Îapplication.WorksheetFunction.XXX¥Îªk½T¦³¤£¦P

1. Sub test2()
   
2.     Dim v As Variant
   
3.     For Each i In Array(2, 7)
   
4.     Debug.Print i
   
5.    
   
6.     ' v is assigned Error 2042
   
7.     v = Application.Match(i, Array(1, 2, 3), 0)
   
8.     Debug.Print "Application"; Tab(25); v
   
9.     Debug.Print "Err.Number"; Tab(25); Err.Number
   
10.     Debug.Print
    
11.    
    
12.     ' trappable error generated, no assignment to v
    
13.     v = Empty
    
14.     On Error Resume Next
    
15.     v = WorksheetFunction.Match(i, Array(1, 2, 3), 0)
    
16.     Debug.Print "WorksheetFunction"; Tab(25); v
    
17.     Debug.Print "Err.Number"; Tab(25); Err.Number
    
18.     Debug.Print
    
19.     On Error GoTo 0
    
20.    
    
21.     ' run-time error, no assignment to v
    
22.     v = WorksheetFunction.Match(i, Array(1, 2, 3), 0)
    
23.    
    
24.     Next
    
25. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 6# GBKEE
ª©¥D¡A½Ð±Ð¤@¤U
¤§«e¦Û¤v¸Õ¼g¡A¨Ï¥Î Application.WorksheetFunction.Match ¥h´M§ä°}¦C¤¸¯À¡A§ä¤£¨ì®ÉÁ`·|¥X²{¿ù»~ 1004¡A¾É­P±j¨î°»¿ù..¨Ï¥Î¤W«Ü¤£¤è«K¡A§ï¥Î Application.Match ¤Ï¦Ó´N¤£·|¤F¡A¤Q¤À¤è«K¡AÃø¹D³o¨â­Ó¬O¤£¦P function ?

¨º»ò Match ¬O¯S¨Ò¶Ü ? ¬O§_¨ä¥L·|¶Ç¦^ #N/A ¿ù»~­Èªº WorksheetFunction  ¨ç¼Æ¡A³£¯à³o¼Ë¥Î©O ?

1. Sub TEST()
   
2.   'If IsError(Application.Match(999, Array(1, 2, 3), 0)) Then MsgBox "Not found"
   
3.   'If IsError(Application.WorksheetFunction.Match(999, Array(1, 2, 3), 0)) Then MsgBox "Not found"
   
4. End Sub

½Æ»s¥N½X

acdx

¦^´_ 7# acdx
±K½X:2945

acdx

¦^´_ 6# GBKEE
¨Ì·Ó±zªº¤è¦¡§ï¦n«á
TextBox1¿é¤J«á­n¿ïTextBox2Ä~Äò¿é¤J®É´N¥X²{¿ù»~

¤U¸ü (43.43 KB)

2013-9-3 17:53

ªþ¤WÀÉ®×:»Ý¦w¸Ë¼W¯q¶°ToolBox
¶}±Òtest.xlsx->°õ¦æ¼W¯q¶°->ÂI¿ïExport Test Result->¿ï¾ÜSound Pressure, ¿é¤JÀW²v½d³ò->"Export >"
Tool.zip (137.85 KB)

¤U¸ü¦¸¼Æ: 9

2013-9-3 18:03

GBKEE

¦^´_ 5# acdx

1. Option Explicit
   
2. Dim Ob(), Msg As Boolean
   
3. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'ªí³æÃö³¬®É·|°õ¦æ¦¹µ{¦¡
   
4.     If Text_Checking(TextBox1) = False Then Cancel = True        'ªí³æ¾nÂI¯d¦bTextBox1¤W
   
5. End Sub
   
6. Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   
7.     If Text_Checking(TextBox2) = False Then Cancel = True
   
8. End Sub
   
9. Private Function Text_Checking(T As MSForms.TextBox) As Boolean
   
10.     Dim I As Variant
    
11.     Text_Checking = True                   '¹w³]¬° True
    
12.     If Msg Then Exit Function              'ªí³æ¤wÃö³¬
    
13.     I = Application.Match(Val(T), Ob, 0)   '§ä¤£¨ì
    
14.     If IsError(I) Then Text_Checking = False
    
15.     If Text_Checking = False Then MsgBox T & " ¤£¬O¥¿½Tªº¼Æ¦r" & vbLf & Join(Ob, vbTab) & vbLf & "¥H¤W¬°¥¿½Tªº¼Æ¦r"
    
16. End Function
    
17. Private Sub UserForm_Initialize()
    
18.     Ob = Array(100, 125, 160, 200, 250, 315, 400, 500, _
    
19.                 630, 800, 1000, 1250, 1600, 2000, 2500, 3150, _
    
20.                 4000, 5000, 6300, 8000, 10000, 12500, 16000, 20000)
    
21. End Sub
    
22. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
23.     Msg = True                'ªí³æÃö³¬®ÉÅܼƳ]¬°  True
    
24. End Sub

½Æ»s¥N½X