­×§ïµ{¦¡½X

man65boy

¦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­Ì¤£§[«ü±Ð~

GBKEE

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

man65boy

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

¤U¸ü (87.41 KB)

2012-1-8 19:38

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

1010108.rar (9.86 KB)

¤U¸ü¦¸¼Æ: 9

2012-1-8 19:37

GBKEE

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

½Æ»s¥N½X

man65boy

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

Hsieh

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

man65boy

¦^´_ 6# Hsieh

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

GBKEE

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

man65boy

¦^´_ 8# GBKEE


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