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

[µo°Ý] ¬Q¤Ñ¶}©l±µÄ²VBA¡A·Q²¤Æµ{¦¡...

[µo°Ý] ¬Q¤Ñ¶}©l±µÄ²VBA¡A·Q²¤Æµ{¦¡...

¤j¤j­Ì¦n¡I
±q³o¸Ì§ä¨ì«D±`¦³¥Îªº¸ê°T
«÷«÷¶K¶K¤§¤U§¹¦¨¤F¤@­Ó²³æªºµ{¦¡
²{¦b¦³¤@­ÓÄæ¦ì¤u¸¹»Ý­n°µ¤ñ¹ï¡A¤ñ¹ï¥¿½T«áÂà´«¦¨©m¦W±a¤Jcombobox
­ì¥»¥Îcase select§¹¦¨¡A¥i¬O50µ§¸ê®Æ§Ú­n¤@µ§¤@µ§¿é¤J...¹ê¦b²´ªá
µ{¦¡¦p¤U
If IsNumeric(TextBox6.Text) Then
     Dim num As Integer
     num = TextBox6.Text
    Else
        If Not IsNumeric(TextBox6.Text) Then
         MsgBox "Äæ¦ì¿é¤J¥²¶·¬°¼Æ­È"
        End If
    End If
    Set y = Sheets("CNC¦W³æ(All1)")
Select Case num
Case "1908"
    ComboBox1.Value = "¶Àxx"
Case "2029"
    ComboBox1.Value = "³¯xx"
Case "2066"
    ComboBox1.Value = "ªLxx"
Case "2145"
    ComboBox1.Value = "³¯yy"
Case "2167"
    ComboBox1.Value = "³¯aa"
Case "2645"
    ComboBox1.Value = "¼Bbb"
Case "2732"
    ComboBox1.Value = "¬xcc"

¥Ø«e¦³¤@­Ósheet±Mªù©ñ³o¨Ç²M³æ
©Ò¥H·Q±qA2¶}©l¤ñ¹ï¤u¸¹¡A¤ñ¹ï¥¿½T«á±N¦¹¤u¸¹ªºBÄæ¦ì©m¦W±a¤Jcombobox1ªºvalue
½Ð°Ý¸Ó¦p¦ó¶i¦æ~~~
°ÝÃD1.png
2016-6-2 16:19


¦³¸Õ¹L³o¼Ë¼g
If IsNumeric(TextBox6.Text) Then
     Dim num As Integer
     num = TextBox6.Text
    Else
        If Not IsNumeric(TextBox6.Text) Then
         MsgBox "Äæ¦ì¿é¤J¥²¶·¬°¼Æ­È"
        End If
    End If
    Set y = Sheets("CNC¦W³æ(All1)")
Select Case num
Case num
    ComboBox1.Value =  y.Range("B2", y.[B2].End(xlDown)).Value
-------------------------------------------------------------------------
¸£³U¹ê¦b¦³ÂI¥´µ²¡A³o¼Ë¼gªk¦³°ÝÃD¶Ü?
«ô°U¦U¦ì¤j¤j«üÂI~~~

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-6-2 22:34 ½s¿è

¦^´_ 1# bsy4life
°ò¥»¤W¹ï©ó¤@¹ï¤@¹ïÀ³¥B¤£·|­«½Æªº±¡§Î,
¥Î Dictionary ¬O¤@ºØº¡®e©ö¹F¨ì»Ý¨Dªº¤è¦¡ :

¤u¸¹¬d¸ß.png
2016-6-2 22:32


== Module==
  1. Public dPeo
½Æ»s¥N½X
== ThisWorkbook ==
  1. Private Sub Workbook_Open()
  2.   Dim lRow&
  3.   
  4.   Set dPeo = CreateObject("Scripting.Dictionary")
  5.   lRow = 2
  6.   With Sheets("Sheet1")
  7.     While .Cells(lRow, 1) <> ""
  8.       dPeo(CStr(.Cells(lRow, 1))) = .Cells(lRow, 2)
  9.       lRow = lRow + 1
  10.     Wend
  11.   End With
  12.   ufMain.Show
  13. End Sub
½Æ»s¥N½X
== UserForm ==
  1. Private Sub cbNo_Change()
  2.   lbName.Caption = dPeo(cbNo.Text)
  3. End Sub

  4. Private Sub UserForm_Initialize()
  5.   Dim vTmp
  6.   
  7.   For Each vTmp In dPeo
  8.     cbNo.AddItem vTmp
  9.   Next
  10.   cbNo.ListIndex = 0
  11. End Sub
½Æ»s¥N½X
®É¶¡¦]¯À³o¸Ì¥u¯à¥ý²©öªºªí²{ : «Ø¥ß¸ê®Æ¹ïÀ³ »P ¬d¸ß ¹ê²{ªº¤è¦¡,
¨ä¥L´N½Ð¦Û¤v°u¤©­×§ï®M¥ÎÅo...

¤u¸¹¯Á¤Þ.zip (10.49 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-6-3 08:22 ½s¿è

¦^´_ 1# bsy4life
luhpro ´£¨ÑªºDictionary ³Ì²«K,¤]¥i¥Î¤u§@ªí¨ç¼Æ CountIf,Match ´À¥N
  1. Private Sub TextBox6_Change()
  2.     Dim xNo As Variant, y As Range
  3.     Set y = Sheets("CNC¦W³æ(All1)").Range("a:a")
  4.     xNo = Application.CountIf(y, Val(TextBox6))  '¶Ç¦^TextBox6¬Û¦Pªº¤º®e¦by ªº­Ó¼Æ
  5.     Select Case xNo
  6.         Case 1   
  7.            'luhpro  ¤W¶Çªº¤u¸¹¯Á¤Þ.xls ¦W³æ¤ºªºÀx¦s®æ¦³¨Ç¬O¤å¦r®æ¦¡
  8.         'y.Value = y.Value   'Âର¼Æ¦r®æ¦¡      
  9.             xNo = Application.Match(Val(TextBox6), y, 0)  '¶Ç¦^TextBox6¦by ªº¦C¸¹
  10.             ComboBox1 = y.Cells(xNo, "b")
  11.         Case 0
  12.             ComboBox1 = ""
  13.          Case Is > 1
  14.             ComboBox1 = "¤u¸¹­«½Æ"
  15.     End Select
  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# luhpro

ÁÂÁÂ luhpro ¤j¤j
§Úªº¸ê½è¾q¶w¡AGBKEE¤j¤jªº¤è¦¡¸Ñ¨M¤F§Úªº°ÝÃD
¥i¬OÁÙ¬O·QÁA¸Ñ¤@¤U¡ADictionaryªº¥Îªk
¥i¬O§Ú¦b¸m´««á¡A°õ¦æµ{¦¡µo¥Í
§Ú¤~¿é¤J¤@­Ó¼Æ­È
¤U­±³o¦æµo¥Í«¬ºA¤£²Å¦X
Label8.Caption = dPeo(TextBox6.Text)

module
  1. Public dPeo
  2. Sub ·s¼W¸ê®Æ()
  3.     fcckeyin.Show
  4. End Sub
½Æ»s¥N½X
ThisWorkBook
  1. Private Sub Workbook_Open()
  2.   Dim lRow&
  3.   Dim dPeo
  4.   
  5.   Set dPeo = CreateObject("Scripting.Dictionary")
  6.   lRow = 2
  7.   With Sheets("CNC¦W³æ(All)")
  8.     While .Cells(lRow, 1) <> ""
  9.       dPeo(CStr(.Cells(lRow, 1))) = .Cells(lRow, 2)
  10.       lRow = lRow + 1
  11.     Wend
  12.   End With
  13.   fcckeyin.Show
  14. End Sub
½Æ»s¥N½X
userform
  1. Private Sub TextBox6_Change() '¿é¤J¤u¸¹Åª¨ú¾Þ§@¤H
  2. TextBox6.IMEMode = fmIMEModeOff
  3.     If IsNumeric(TextBox6.Text) Then
  4.      Dim num As Integer
  5.      num = TextBox6.Text
  6.     Else
  7.         If Not IsNumeric(TextBox6.Text) Then
  8.          MsgBox "Äæ¦ì¿é¤J¥²¶·¬°¼Æ­È"
  9.         End If
  10.     End If
  11.     Label8.Caption = dPeo(TextBox6.Text)  '¥»¨Ó¨Ï¥Îcombobox¡A¤£ª¾¹D¦p¦ó¨ú¥N©Ò¥H¥Îlabel´ú¸Õ
  12. End Sub

  13. Private Sub fcckeyin_Initialize()
  14.   Dim vTmp
  15.   For Each vTmp In dPeo
  16.     TextBox6.Text = vTmp
  17.   Next
  18.   '¥»¨Ó¦¹¦æ¬OcbNo.ListIndex = 0 ¥i¬O§Ú¬O¥Îtextbox¡A©Ò¥H¦¹¦æ®³±¼
  19. End Sub
½Æ»s¥N½X
½Ð°Ý¬O¤£¬O§Úªº¼gªk¦³»~?
ÁÂÁ¦U¦ì¤j¤j~~
¤]ÁÂÁÂGBKEEÀ°§Ú¸Ñ¨M°ÝÃD­ò~~!!

TOP

¦^´_ 3# GBKEE

¤£¦n·N«ä¡AÁöµM°ÝÃD¸Ñ¨M¤F
¦ý¹ï³o¬qµ{¦¡½XÁÙ¬O¨S¦³«Ü²z¸Ñ
  1.   Dim xNo As Variant, y As Range  '³oÃ䬰¤°»òxNo­n³]©w¦¨ VariantÃþ«¬©O?
  2.     Set y = Sheets("CNC¦W³æ(All1)").Range("a:a")  '³o¸Ìªºa:a ¤£»Ý­n©w¸q¶Ü? ¨º¥¦¥Nªíªº­È¬°¦ó?©Ò¦³A¦æA¦Cªº¸ê®Æ¶Ü~~
  3.     xNo = Application.CountIf(y, Val(TextBox6))  '¶Ç¦^TextBox6¬Û¦Pªº¤º®e¦by ªº­Ó¼Æ<<³o¤@¬q¬O­n§PÂ_§Ú¤u¸¹¦W³æ¬O§_¬O°ß¤@­È¶Ü?
  4.     Select Case xNo
  5.         Case 1   
  6.            'luhpro  ¤W¶Çªº¤u¸¹¯Á¤Þ.xls ¦W³æ¤ºªºÀx¦s®æ¦³¨Ç¬O¤å¦r®æ¦¡  <§ÚÁÙ¤£¯à¸ü><
  7.         'y.Value = y.Value   'Âର¼Æ¦r®æ¦¡       <³o¤@¦æ¬O¤°»ò·N«ä©O? ³o¦³Âà®æ¦¡¶Ü?
  8.             xNo = Application.Match(Val(TextBox6), y, 0)  '¶Ç¦^TextBox6¦by ªº¦C¸¹  
  9.             ComboBox1 = y.Cells(xNo, "b")  
  10.         Case 0
  11.             ComboBox1 = ""
  12.          Case Is > 1
  13.             ComboBox1 = "¤u¸¹­«½Æ"
½Æ»s¥N½X
³Â·ÐGBKEE¤j¤j¬°§Ú¸Ñ»¡¤@¤U><
¨ä¹ê¥»¨Ó°ÝÃD§ó¦h
µM«á¤~­èµo²{¥i¥H¥Îexcelªº¨ç¼Æ¡A¹ê»Ú´ú¸Õ§¹´N¤ñ¸û²M·¡¤F
ÁÂÁ«e½ú~~

TOP

¥t¥~ÁÙ¦³¤@­Ó¤p°ÝÃD...
§ÚÁÙ°µ¤F¤@­Ó¸ü¤J²M³æªº³¡¤À
¦]¬°¦³¥i¯à¿é¤JªÌ¤£¬O¥»¤H¡A©Ò¥H¤£·|ª¾¹D¤u¸¹
¤À¶}¨Ó¥Î³£¬O¥¿±`ªº
¦ý¥u­n¥ý¸ü¤J²M³æ¨â¦¸¥H¤W
¦A¥h¿é¤J¤u¸¹¬d¸ß
ComboBox1 = y.Cells(xNo, "b")  
´N·|¿ù»~~~
°ÝÃD2-2.png
2016-6-3 13:13

°ÝÃD2.png
2016-6-3 13:13
  1. Private Sub CommandButton4_Click() 'Ū¨ú¾Þ§@¤H­û
  2.         If OptionButton1.Value = True Then
  3.         Set x = Sheets("¾Þ§@¤H­û(¦­¯Z)")
  4.         End If
  5.         If OptionButton2.Value = True Then
  6.         Set x = Sheets("¾Þ§@¤H­û(¤¤¯Z)")
  7.         End If
  8.         If OptionButton3.Value = True Then
  9.         Set x = Sheets("¾Þ§@¤H­û(±ß¯Z)")
  10.         End If
  11.         ComboBox1.List = x.Range("B2", x.[B2].End(xlDown)).Value
  12. End Sub
  13.                         'Private Sub CommandButton5_Click() '²M°£¾Þ§@¤H­û
  14.                         'TextBox6.SetFocus
  15.                         'ComboBox1.Value = ""
  16.                         'TextBox6.Text = ""
  17.                         'For j = ComboBox1.ListCount To 1 Step -1
  18.                         'Me.ComboBox1.RemoveItem j - 1
  19.                         'Next
  20.                         'End Sub
  21.                         
  22. Private Sub TextBox6_Change() '¿é¤J¤u¸¹Åª¨ú¾Þ§@¤H
  23. TextBox6.IMEMode = fmIMEModeOff
  24.     If IsNumeric(TextBox6.Text) Then
  25.      Dim num As Integer
  26.      num = TextBox6.Text
  27.     Else
  28.         If Not IsNumeric(TextBox6.Text) Then
  29.          MsgBox "Äæ¦ì¿é¤J¥²¶·¬°¼Æ­È"
  30.         End If
  31.     End If
  32.      Dim xNo As Variant, y As Range
  33.     Set y = Sheets("CNC¦W³æ(All)").Range("a:a")
  34.     xNo = Application.CountIf(y, Val(TextBox6))  '¶Ç¦^TextBox6¬Û¦Pªº¤º®e¦by ªº­Ó¼Æ
  35.     Select Case xNo
  36.         Case 1
  37.             xNo = Application.Match(Val(TextBox6), y, 0)  '¶Ç¦^TextBox6¦by ªº¦C¸¹
  38.             ComboBox1 = y.Cells(xNo, "b")   <<<°»´ú¨ì¦¹¦æ
  39.         Case 0
  40.             ComboBox1 = ""
  41.          Case Is > 1
  42.             ComboBox1 = "¤u¸¹­«½Æ"
  43.     End Select
  44. End Sub
½Æ»s¥N½X
§Ú¬O¤£¬O§â¨Ï¥ÎªÌ·Q±o¤Ó¥Õ¥ØXD

TOP

¦^´_ 6# bsy4life

¦³¿ù»~,¤W¶ÇÀɮ׬ݬÝ

Application.Match ,¬dµL®É¶Ç¦^¿ù»~­È,©Ò¥HÅܼƲߺD Type As Variant
³oµ{¦¡Application.Match ¦³·f°tApplication.CountIf ¶Ç¦^ªº­È,¥iÁ׶}¿ù»~­È.
©Ò¥HxNo¥i¥H¤£³]©w¦¨ VariantÃþ«¬.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE

¤wªþ¤WÀÉ®×~~
¥Í²£¹êÁZ®Ä²v¹Bºâ0604.rar (116.23 KB)
ÁÂÁ«e½ú!!

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-6-5 00:54 ½s¿è
¦^´_  luhpro
ÁÂÁÂ luhpro ¤j¤j
§Úªº¸ê½è¾q¶w¡AGBKEE¤j¤jªº¤è¦¡¸Ñ¨M¤F§Úªº°ÝÃD
¥i¬OÁÙ¬O·QÁA¸Ñ¤@¤U¡A ...
bsy4life µoªí©ó 2016-6-3 09:43

­Ó¤H¬Ý¨ìªº±¡§Î´£¥X²L¨£¨Ñ§A°Ñ¦Ò:
1.
Module ¤W³]©w Public dPeo ¬° "¥þ°ìÅܼÆ", (¾ã­ÓÀÉ®×¥ô¦ó¦a¤è³£¬O¨Ï¥Î¦P¤@­Óª«¥ó©ÎÅܼÆ)
¦ý§A¦b ThisWorkBook ¤S³]¤F¤@­Ó Dim dPeo,
Dim ªº dPeo «h¬° °Ï°ìÅܼÆ,
¥u¦³³o­Ó Sub ¬Ýªº¨ì,
°õ¦æ¨ì End Sub «á -> ¥X¥h Sub ´N¦Û°Ê³Q®ø·À¤F,

¦Ó¦]»P Module ªº dPeo ÅܼƦWºÙ¬Û¦P,
³o­Ó Sub ¤¤¨Ï¥Î¨ìªº dPeo ¤@«ß¹w³]¨Ï¥Î Dim ¥X¨Óªº³o­Ó "°Ï°ìÅܼÆ",
¤]´N¬O³o­Ó Sub ¤¤ªº½á­È§@·~µ²ªG¦b¨ä¥L¥ô¤@ Sub §¹¥þµLªk¨Ï¥Î)

§Aªº Label8.Caption = dPeo(TextBox6.Text) ·|µo¥Í¿ù»~¬O¦]¬° UserForm ³o­Ó Sub ¬Ý¨ìªº dPeo ¬OªÅªº.
(¥i¥H¦b°õ¦æ¨ì¦¹¦æµo¿ù»~®ÉÂI "°»¿ù" «ö¶s, ¦A©ó "§Y®É¹Bºâµøµ¡" ¤U¦¹«ü¥O:   debug.print dpeo.count     §A·|¬Ý¨ìµ²ªG¬O 0 )
©Ò¥H ThisWorkBook ªº Dim dPeo ¥²¶·®³±¼, ¨ä¥L Sub ¤~¯à¥¿±`¨Ï¥Î¨ì ThisWorkBook ½á¤© dPeo ªº¸ê®Æ.

2. userform ¤¤:
    If IsNumeric(TextBox6.Text) Then
....
    Else
        If Not IsNumeric(TextBox6.Text) Then  <- ³o¤@¦æ¬O¦h¾lªº If  X  Then ... Else «ü¥Oªº Else ´N¬O ·í Not X ªº±ø¥ó¬° "¯u" ®É°õ¦æ.
...
        End If     <-  ·f°t IF ªº«ü¥O, ·í IF ®³±¼®É¤]­n¸òµÛ®³±¼

    End If
    Label8.Caption = dPeo(TextBox6.Text)  '¥»¨Ó¨Ï¥Îcombobox¡A¤£ª¾¹D¦p¦ó¨ú¥N©Ò¥H¥Îlabel´ú¸Õ
'¤£ª¾¬°¦ó­n¥Î combobox ?
·í¨Ï¥ÎªÌ¦b TextBox ¿é¤J¤º®e«á,
³o¸ÌÀ³¸Ó¥u¦³°ß¤@ªº­È(¥Î Label ª«¥ó´N¥i¥H¤F),
¦Ó¤£¬O¦³¦n´X­Ó­Èªº²M³æª«¥ó.
°£«D§A·Q­nÅý¨Ï¥ÎªÌ±µµÛ¥i¥H¦A¦b combobox ÂI¿ï ¾Þ§@¤H ¥H¦A¦¸Åܧó TextBox ªº¤º®e.
µù : ­Y¦³¦¹»Ý¨D, ¥i¥H¥Î combobox.AddItem ... ¥H¼W¥[²M³æ¶µ¥Ø

End Sub

³Ì«á... 8# ªºÀɮצ³³]±K½X, µ{¦¡¬Ý¤£¨ì...

ÃB¥~«Øij :
CNC¦W³æ(All) ¦pªGÀ³¸Ó¬O¥]§t¥þ³¡  ¾Þ§@¤H­û(¦­, ¤¤, ±ß¯Z) ªº¸Ü,
¥i¥H¦Ò¼{¥Î Dictionary ¦b¶}Àɮɦb¤T­Ó Sheet ¤À§O§ì¨ú,
µM«á¦b CNC¦W³æ(All) ¤W°ÊºA«Ø¥ß²M³æ.

TOP

¦^´_ 9# luhpro

¤£ª¾¬°¦ó­n¥Î combobox ?
·í¨Ï¥ÎªÌ¦b TextBox ¿é¤J¤º®e«á,
³o¸ÌÀ³¸Ó¥u¦³°ß¤@ªº­È(¥Î Label ª«¥ó´N¥i¥H¤F),
¦Ó¤£¬O¦³¦n´X­Ó­Èªº²M³æª«¥ó.
°£«D§A·Q­nÅý¨Ï¥ÎªÌ±µµÛ¥i¥H¦A¦b combobox ÂI¿ï ¾Þ§@¤H ¥H¦A¦¸Åܧó TextBox ªº¤º®e.
µù : ­Y¦³¦¹»Ý¨D, ¥i¥H¥Î combobox.AddItem ... ¥H¼W¥[²M³æ¶µ¥Ø


¦]¬°²{¦bÁÙ¤£½T©wµ{¦¡·|¨ì²{³õ¨Ñ¾Þ§@¤H¨Ï¥Î
ÁÙ¬Oµ¹¿ì¤½«Çªº¤º¶Ô¤H­û¨Ï¥Î
¦pªG¬Oµ¹²{³õªº¨Ï¥Î¡A¨º¿é¤J¤u¸¹·|¬O³Ì§Öªº¤è¦¡
¦pªG¬O¤º¶Ô¤H­û¡A¤]¤£¤Ó¥i¯à§â¨C¤@­Ó¤Hªº¤u¸¹¼ô°O
©Ò¥H¬°¤F¤è«K¥L­ÌÆF¬¡¹B¥Î¡A¤~³]­p¦¨¤u¸¹¬d¸ß¡A¸òŪ¨ú²M³æ¨âºØ¼Ò¦¡\
À³¸ÓÁÙ·|¦A§â¥¦Åܦ¨±½±ø½X¥i¥Hª½±µÅª¨úªº¤è¦¡
ÁÙ¦³ÁÙ¦³
±K½X¬O1215¡A©êºp§Ñ¤F¸Ñ¶}@@


ÁÂÁÂluhpro ¤j¤j­@¤ßªº¸Ñµª~~
§Aªº«Øij§Ú·|¸Õ¸Õ¬Ý!!

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD