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

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

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

§Ú­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?

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-9-4 12:26 ½s¿è

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦^´_ 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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-9-3 20:05 ½s¿è

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

TOP

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

TOP

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

ªþ¤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)

TOP

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

TOP

        ÀR«ä¦Û¦b : ¹ï¤÷¥À­nª¾®¦¡A·P®¦¡B³ø®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD