¥i¥H§Q¥ÎVBA¶i¦æ¦h±ø¥ó¿z¿ï¶Ü? VBA±ø¥ó¿z¿ï

Jared

·Q½Ð°Ý¤j¤j
ÁöµMOffice 2007¦³¤º«Ø¿z¿ï¥\¯à
¦ý¦pªG§Ú·Q­n±N¿z¿ï¥\¯à¼g¦b¤@­Óªí³æ¸Ì­±
¥i¥H¤@¦¸¶i¦æ¦h±ø¥ó¿z¿ï¸ê®Æ¶Ü?(¦p¹Ï)

2013-3-28 16:43

¦]¬°¥Ø«e§Ú¥u¦³¼g¤@¦¸¿é¤J³æµ§¸ê®Æ¥H©Ê¦W¬°·j´M¥Ø¼Ð
µ{¦¡¹B¦æµ²ªG¦p¹Ï

2013-3-28 16:47

¦ý¦pªG§Ú·Q­n±N·j´M±ø¥ó¬°¨âµ§¥H¤W(¼Ò½k·j´M)
¬Æ¦Ü¨ì§¹¥þ²Å¦X
±N¿z¿ïµ²ªGÅã¥Ü¦b­ì¨Óªºªí³æ(¥¼²Å¦X±ø¥óªº¸ê®ÆÁôÂÃ(§éÅ|°_¨Ó))
³o¼Ëªº¼gªk´N¨S¿ìªk¤F:Q
¥Ø«eµ{¦¡¼gªk¦p¤U(¶¶±aªþ¤Wªí³æ)
´ú¸ÕÀÉ(¿z¿ï±ø¥ó).zip (53.77 KB)

¤U¸ü¦¸¼Æ: 68

2013-3-28 16:49

§Æ±æ¦³¤j¤j¯à¸Ñ¨M§ÚªººÃ´b>"<

1. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   
2. 
   
3. With Sheet2
   
4. 
   
5. Set a = .Columns("A").Find(TextBox1.Text, lookat:=xlWhole, LookIn:=xlValues)
   
6. If a Is Nothing Then
   
7. MsgBox "¬dµL¦¹¡A½Ð½T»{«á¦A¿é¤J¡I"
   
8. 
   
9. Exit Sub
   
10. End If
    
11. 
    
12. ComboBox1.Value = a.Offset(, 1) '¦~°O
    
13. ComboBox2.Value = a.Offset(, 2) '¨­°ª
    
14. ComboBox3.Value = a.Offset(, 3) 'Åé­«
    
15. TextBox2.Value = a.Offset(, 4) '¦í§}
    
16. 
    
17. 
    
18. End With
    
19. End Sub
    
20. Private Sub CommandButton2_Click()
    
21. Me.ComboBox1 = ""
    
22. Me.ComboBox2 = ""
    
23. Me.ComboBox3 = ""
    
24. Me.TextBox1 = ""
    
25. Me.TextBox2 = ""
    
26. End Sub
    
27. 
    
28. Private Sub CommandButton3_Click()
    
29. Test5.Hide
    
30. End Sub
    
31. 
    
32. Private Sub UserForm_Initialize() '¤@¶}©lªº³]©w
    
33. Dim i
    
34. For i = 10 To 18
    
35.     ComboBox1.AddItem i
    
36. Next
    
37. 
    
38. For i = 120 To 160
    
39.     ComboBox2.AddItem i
    
40. Next
    
41. 
    
42. For i = 30 To 70
    
43.     ComboBox3.AddItem i
    
44. Next
    
45. End Sub

½Æ»s¥N½X

Jared

¦^´_ 1# Jared


µo²{§Q¥Î³°»s¥¨¶°ªº¤è¦¡
¥i¥H¤@¦¸¿z¿ï¤@­Ó±ø¥ó
¨º¦pªG¬O­n¨â­Ó¥H¤W
­n«ç»ò³]©w©O?

1. Private Sub CommandButton4_Click() '¿z¿ï±ø¥ó
   
2. Dim a, b, c, d, e
   
3. a = Test5.TextBox1.Text
   
4. b = Test5.ComboBox1.Value
   
5. c = Test5.ComboBox2.Value
   
6. d = Test5.ComboBox3.Value
   
7. e = Test5.TextBox2.Text
   
8. If a <> "" Then
   
9. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=1, Criteria1:=a: Exit Sub
   
10. End If
    
11. 
    
12. If b <> "" Then
    
13. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=2, Criteria1:=b: Exit Sub
    
14. End If
    
15. 
    
16. If c <> "" Then
    
17. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=3, Criteria1:=c: Exit Sub
    
18. End If
    
19. 
    
20. If d <> "" Then
    
21. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=4, Criteria1:=d: Exit Sub
    
22. End If
    
23. 
    
24. If e <> "" Then
    
25. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=5, Criteria1:=e: Exit Sub
    
26. End If
    
27. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 2# Jared

1. Option Explicit
   
2. Private Sub CommandButton4_Click() '¿z¿ï±ø¥ó
   
3. Dim a, b, c, d, e
   
4. a = Test5.TextBox1.Text
   
5. b = Test5.ComboBox1.Value
   
6. c = Test5.ComboBox2.Value
   
7. d = Test5.ComboBox3.Value
   
8. e = Test5.TextBox2.Text
   
9. With ActiveSheet.Range("$A$1:$E$12")
   
10.     .Parent.AutoFilterMode = False   'Åã¥Ü¥þ³¡¸ê®Æ ->·sªº ¦h­«¿z¿ï ¤~·|½T.
    
11.     '¦h­«¿z¿ï  ..........
    
12.     If a <> "" Then .AutoFilter Field:=1, Criteria1:=a
    
13.     If b <> "" Then .AutoFilter Field:=2, Criteria1:=b
    
14.     If c <> "" Then .AutoFilter Field:=3, Criteria1:=c
    
15.     If d <> "" Then .AutoFilter Field:=4, Criteria1:=d
    
16.     If e <> "" Then .AutoFilter Field:=5, Criteria1:=e
    
17.     '¦h­«¿z¿ï  ..........
    
18. End With
    
19. End Sub

½Æ»s¥N½X

Jared

¦^´_ 3# GBKEE


·PÁ¤j¤j¸Ñ°£¤F§ÚªººÃ´b
­ì¥»ªºµ{¦¡½X¬O¼g¦¨¿W¥ß¦¡

1. If a <> "" Then
   
2. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=1, Criteria1:=a: Exit Sub
   
3. End If

½Æ»s¥N½X

«á¨Ó¦³­×§ï¦¨

1. Private Sub CommandButton4_Click()
   
2. Dim a, b, c, d, e
   
3. a = Test5.TextBox1.Text
   
4. b = Test5.ComboBox1.Value
   
5. c = Test5.ComboBox2.Value
   
6. d = Test5.ComboBox3.Value
   
7. e = Test5.TextBox2.Text
   
8. 
   
9. If a <> "" Or b <> "" Or c <> "" Or d <> "" Or e <> "" Then
   
10. With Sheet2.Range("$A$1:$E$12")
    
11. .AutoFilter Field:=1, Criteria1:=a
    
12. .AutoFilter Field:=2, Criteria1:=b
    
13. .AutoFilter Field:=3, Criteria1:=c
    
14. .AutoFilter Field:=4, Criteria1:=d
    
15. .AutoFilter Field:=5, Criteria1:=e
    
16. 
    
17. End With
    
18. End If
    
19. End Sub

½Æ»s¥N½X

¦ý«oÅܦ¨­n¨C­Ó±ø¥ó³£²Å¦X¤~·|¿z¿ï¥XªF¦è>"<
¬Ý¤F¤j¤jªº¸Ñµª
­ì¨Ó¬O­n¤À¶}¶i¦æ

¬Ý¨Ó§Ú¤S¾Ç¨ì¤@½Ò¤F^¤f^
«D±`·PÁ¡I¡I

jeffrey628litw

2¦ì¤j¤j¡A¤p§Ì­è¦b¾Ç²ßVBA¡A¥i¥H±N±z­Ì°Q½×ªºµ²ªG¡AÀɮפW¶Ç¨Ñ§Ú­Ì¤U¸ü¶Ü?·P®¦

Jared

¦^´_ 5# jeffrey628litw


§A¥i¥H§â¼Ó³»°ÝÃDªº´ú¸ÕÀɤU¸ü¤U¨Ó
¦b§âµ{¦¡½X´À´«±¼´N¥i¥H¤F
¤¶­±¤WÀ³¸Ó¬O¨S¤°»ò§ï¨ì
¥u¦³¦bµ{¦¡½Xªº³¡¤À

bsofsoratest

ÁÂÁ¤À¨É¡C

Qektyyrwp

¦^´_ 4# Jared


   a = Test5.TextBox1.Text
b = Test5.ComboBox1.Value
c = Test5.ComboBox2.Value
d = Test5.ComboBox3.Value¡C¤j¤j½Ð°Ýtest5¡C³o­Ó¦a¤è¬O«ç»ò³]­pªº¡C¤p§ÌÅv­­¤£¨¬¤£¯à¤U¸ü¨Ó¬Ý¡C©êºp

Qektyyrwp

¦^´_ 3# GBKEE


    µo²{³o¬O´X¦~«eªº©«¤l¤F¡C´«°Ý°Ýª©¥D¡C
a = Test5.TextBox1.Text
b = Test5.ComboBox1.Value
c = Test5.ComboBox2.Value
d = Test5.ComboBox3.Value¡C¤j¤j½Ð°Ýtest5¡C³o­Ó¦a¤è¬O«ç»ò³]­pªº¡C¤p§ÌÅv­­¤£¨¬¤£¯à¤U¸ü¨Ó¬Ý¡C©êºp

Michelle-W

¦^´_ 3# GBKEE

ª©¥D¤j¤j±z¦n¡A
§Úªº¦h±ø¥ó¿z¿ï¥\¯àÁ`¬O¥¢±Ñ...¥i§_¨ó§UÀ°¦£¬Ý¬Ý­þÃä¿ù¤F
¦Ó¥BÀ³¸Ó¦p¦ó±N¿z¿ï§¹ªº¸ê®Æ¶K¨ì¤¶­±¤W(¨Ì¾Ú¤£¦PÄ檺¦WºÙ)
¥ýÁÂÁ±z¤F!
ºÃ°Ý..rar (34.68 KB)

¤U¸ü¦¸¼Æ: 1

2017-7-20 09:13