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

­×§ïµ{¦¡½X

­×§ïµ{¦¡½X

¦p¦óÅý¥H¤Uªºµ{¦¡¥]§t¦b§ä¤£¨ì­û¤u½s¸¹¡A¯à´£¿ô¡A§Æ±æ¦b¦h¥[¤W¦pªG¤£·Q¦bTextBox3¤W¿é¤J½s¸¹(ªÅ¥Õ)ªº·N«ä¡A´Nª½±µ¦b¬¡­¶§e²{ªÅ¥Õªº«ü¥O¡A

Private Sub CommandButton1_Click()
    On Error Resume Next
    '¿é¤J­û¤u½s¸¹«á¥Î ¨ç¼Æ¨Ó¨ú±o­û¤u©m¦W
    ranme = sht2.Cells(WorksheetFunction.Match(TextBox3.Text, sht2.Range("D:D"), 0), "E")
    '¦pªG¬dµL­û¤u½s¸¹¡A­n¨D­«·s¿é¤J­û¤u½s¸¹
    If ranme = "" Then
        MsgBox "¬dµL­û¤u½s¸¹¡A½Ð­«·s¿é¤J"
        UserForm1.TextBox3.Text = ""
        UserForm1.TextBox3.SetFocus
        Exit Sub
    End If
    On Error GoTo 0
    With sht1
        .Range("A2") = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 0)
        .Range("A4") = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
        .Range("B4") = TextBox2.Text
        .Range("B7") = ranme '³o¸Ì¬OTextBox3Âà¼g¦a¤è
    End With
End Sub

³Â·Ð¦U¦ì¥ý¶i­Ì¤£§[«ü±Ð~

¦^´_ 1# man65boy
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim ranme As Variant
  4.     '¿é¤J­û¤u½s¸¹«á¥Î ¨ç¼Æ¨Ó¨ú±o­û¤u©m¦W
  5.     ranme = Application.Match(TextBox3.Text, Sht2.Range("D:D"), 0)
  6.     '¦p§ä¨ì ranme->¶Ç¦^¼Æ¦r ,§_«h ranme ->¿ù»~­È "# N/A"
  7.     If IsError(ranme) Then    '¦pªG¬dµL­û¤u½s¸¹¡A­n¨D­«·s¿é¤J­û¤u½s¸¹
  8.         MsgBox "¬dµL­û¤u½s¸¹¡A½Ð­«·s¿é¤J"
  9.         UserForm1.TextBox3.Text = ""
  10.         UserForm1.TextBox3.SetFocus
  11.         Exit Sub
  12.     End If
  13.     With Sht1
  14.         .Range("A2") = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 0)
  15.         .Range("A4") = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
  16.         .Range("B4") = TextBox2.Text
  17.         .Range("B7") = TextBox3     '³o¸Ì¬OTextBox3Âà¼g¦a¤è
  18.     End With
  19. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ man65boy ©ó 2012-1-8 20:47 ½s¿è

¦^´_ 2# GBKEE


   
    ­«ÂI¬O­n:¦³¿é¤J®É¡A§ä¤£¨ì­û¤u½s¸¹¡A¯à°÷´£¿ô¡A¦b¦h¥[¤W¦pªG¤£·Q¦bTextBox3¤W¿é¤J½s¸¹¡A¥u¿é¤JªÅ¥Õ¡A«öOK´Nª½±µ¦b¬¡­¶§e²{ªÅ¥Õ¡A

   ½Æ»s¦Ñ®v©Ò§ïªºµ{¦¡¡A¥X²{ ¡A ¤p§Ì§â­ì©lªí³æ²¾¤@¨Ç¹L¨Óµ¹¦Ñ®v¹L¥Ø¡A¤p§ÌVBAµ{«×®t«Ü¦h¡A©È®ö¶O¦Ñ®vªº®É¶¡¡A

1010108.rar (9.86 KB)

TOP

¦^´_ 3# man65boy
  1. Dim sht1 As Worksheet
  2. Dim sht2 As Worksheet
  3. Private Sub ComboBox1_Change()
  4.     Dim i As Integer
  5.     i = ComboBox1.ListIndex    'ListIndex ÄÝ©Ê ¥Î¨ÓÃѧO ListBox ©Î ComboBox ¤¤ªº¥Ø«e¿ï¨ú±M®×

  6.     With sht1
  7.         If i < 0 Then         ' -1 ®É ComboBox1ªº¿ï¨ú¤£¦bComboBox1.Listªº½d³ò
  8.             TextBox3.Text = ""            '
  9.             .Range("B7") = ""
  10.         Else
  11.             TextBox3 = ComboBox1.List(i, 1)          ' sht2.Range("d2:e" & r).Value: ²Ä2Äæ:©m¦W
  12.             .Range("B7") = ComboBox1.List(i, 1)
  13.         End If
  14.     End With
  15. End Sub
  16. Private Sub UserForm_Initialize()
  17.     Dim r As Integer
  18.     Set sht1 = Sheets("¦C¦L")
  19.     Set sht2 = Sheets("¸ê®Æªí")
  20.     r = sht2.Range("D65536").End(xlUp).Row
  21.     With ComboBox1
  22.         .List = sht2.Range("D2:E" & r).Value   '³]¸mComboBox1ªºListªº¤º®e
  23.         .ColumnCount = 1                       '¥uÅã¥Ü ²Ä1Äæ:½s¸¹   ,²Ä2Äæ:©m¦W->¤£Åã¥Ü
  24.         .Style = 0                             'ComboBox ¬°¤@­Ó¤U©Ô¦¡²M³æ¡C ¨Ï¥ÎªÌ©Î±q¤U©Ô¦¡²M³æ¤¤¿ï¨ú¥i¦b¤å¦r°Ï¿é¤J ( ¹w³] )¡C         '
  25.         .MatchRequired = Fasle                 'MatchRequired Äݩʬ° True¡A¨Ï¥ÎªÌ´N¥²¶·¿é¤J²Å¦X²M³æ¤¤¬J¦³±M®×ªº¤å¦r¡A¤~¥iÂ÷¶} ComboBox¡CÂǵۭn¨D¨Ï¥ÎªÌ¿ï¨ú¬J¦³ªº±M®×¡A¥i¥H¨Ï MatchRequired ºûÅ@²M³æªº§¹¾ã©Ê¡C
  26.         .TabIndex = 1                          '³]©w¦bªí³æ¤¤ªº©w¦ì¶¶§Ç¡C
  27.     End With
  28. End Sub
  29. Private Sub CommandButton2_Click()
  30.     Unload Me
  31. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ man65boy ©ó 2012-1-10 19:22 ½s¿è

¦^´_ 4# GBKEE

·PÁÂGBKEE¤j¤jªº¦^µª¡A·Q½Ð¤j¤j¦b­×§ï¤@¨Ç¡A¤p§Ì¥i¯à§â½d¨Ò¦h¥[¤FComboBox1¤U©Ô¡A©ç¨Æ¡A¤p§Ì¿ù¬Ýµ{¦¡»Ý­nComboBox1¡A¤£¹L¤]ÁÂÁ¤j¤jµ¹¤F¤p§Ì¤£¿ùªº½d¨Ò¡A§Ú¦A§âÀÉ®×­«·s¤W¶Ç¡A³o¦¸³¡·|¿ù¤F¡C

10101090.rar (12.62 KB)

TOP

¦^´_ 5# man65boy
  1. Private Sub CommandButton1_Click()
  2. Dim A As Range
  3.     Set A = sht2.[D:D].Find(TextBox3.Text, lookat:=xlWhole)
  4.     '¦pªG¬dµL­û¤u½s¸¹¡A­n¨D­«·s¿é¤J­û¤u½s¸¹
  5.     If A Is Nothing Then '¦pªG½s¸¹¤£¦s¦b
  6.         MsgBox "¬dµL­û¤u½s¸¹¡A½Ð­«·s¿é¤J"
  7.         UserForm1.TextBox3.Text = ""
  8.         UserForm1.TextBox3.SetFocus
  9.         Exit Sub
  10.     End If
  11.     With sht1
  12.         .Range("B7") = A.Offset(, 1)
  13.     End With
  14. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh

·PÁ¶W¯Åª©¥Dªº¸ÑÃD¡A¤p§Ì·P®¦¦b¤ßÀY¡A¯¬ª©¥D"Às¦~¦æ¤j¹B~µo¤j°]" ·s¦~§Ö¼Ö!

TOP

¦^´_ 5# man65boy
  1. Dim sht1 As Worksheet, sht2 As Worksheet
  2. Private Sub CommandButton1_Click()
  3.     Dim ranme As Variant
  4.     '¿é¤J­û¤u½s¸¹«á¥Î Match ¨ç¼Æ¨Ó¨ú±o­û¤u©m¦W
  5.     ranme = Application.Match(TextBox3.Text, sht2.Range("D:D"), 0)
  6.     '¦pªG¬dµL­û¤u½s¸¹¡A­n¨D­«·s¿é¤J­û¤u½s¸¹
  7.     With sht1
  8.         If IsError(ranme) Then '¦p§ä¨ì ranme->¶Ç¦^¼Æ¦r ,§_«h ranme ->¿ù»~­È "# N/A"
  9.             MsgBox "¬dµL­û¤u½s¸¹¡A½Ð­«·s¿é¤J"
  10.             UserForm1.TextBox3.Text = ""
  11.             UserForm1.TextBox3.SetFocus
  12.             .Range("B7") = ""
  13.         Else
  14.             .Range("B7") = sht2.Range("E" & ranme)
  15.         End If
  16.     End With
  17. End Sub
  18. Private Sub CommandButton2_Click()
  19.     Unload Me
  20. End Sub
  21. Private Sub UserForm_Initialize()
  22.     Set sht1 = Sheets("¦C¦L")
  23.     Set sht2 = Sheets("¸ê®Æªí")
  24. End Sub
½Æ»s¥N½X

TOP

¦^´_ 8# GBKEE


    ·PÁÂGBKEE¤j¤jªº¸ÑÃD¡AÅý¤p§Ì¤S¦h¤F¤£¤@¼Ëµ{¦¡½X°Ñ¦Ò¾Ç²ß¡A·PÁ§A¡A¤]¯¬§A"Às¦~¦æ¤j¹B~°]·½ºu¨Ó" ·s¦~§Ö¼Ö!

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD