GBKEE

¦^´_ 1# JEAN
2# mark15jill  »¡ªº¬O

¸Õ¸Õ¬Ý ¬O³o¤è¦V¶Ü?


test.rar (13.33 KB)

¤U¸ü¦¸¼Æ: 21

2012-10-18 16:36

GBKEE

¦^´_ 5# JEAN
¸Õ¸Õ¬Ý

1. '[»â¥Î³æ]³¡ªù³æ¦ì¡G B3 ¦³ÅÜ°Ê ->¥Ó½Ð¤H¦W¦r¡G B4 ·|§óµÛÅܰʦW³æ
   
2. Private Sub CommandButton1_Click()
   
3.     ­º­¶
   
4. End Sub
   
5. Private Sub CommandButton2_Click()
   
6.     Sheets("»â¥Î°O¿ý©ú²Óªí").Visible = True
   
7.     Sheets("»â¥Î°O¿ý©ú²Óªí").Select
   
8.     Me.Visible = False
   
9. End Sub
   
10. Private Sub Worksheet_Activate() '»s©w³¡ªù³æ¦ìªºÅçÃÒ, [»â¥Î³æ]¤u§@ªí¬°§@¥Î¤¤®Éªº¹w³]¥ó
    
11.     Dim Ar As String, xI As Integer
    
12.     With Sheets("¤H­û¸ê®Æ").[E2]
    
13.         xI = 2
    
14.         Do While .Cells(xI) <> ""
    
15.             If Ar = "" Then
    
16.                 Ar = "," & .Cells(xI, 1)
    
17.             Else
    
18.                 If InStr(Ar, "," & .Cells(xI)) & "," = 0 Then Ar = Ar & "," & .Cells(xI)
    
19.             End If
    
20.             xI = xI + 1
    
21.         Loop
    
22.     End With
    
23.     With Range("B3").Validation
    
24.         .Delete
    
25.         If Ar <> "" Then .Add xlValidateList, , , Mid(Ar, 2)
    
26.     End With
    
27. End Sub
    
28. Private Sub Worksheet_Change(ByVal Target As Range)
    
29.     Application.EnableEvents = False
    
30.     If Target(1).Address(0, 0) = "B3" Then
    
31.         If Target(1) = "" Then Range("B4") = ""
    
32.         ÅçÃÒB4
    
33.     End If
    
34.     Application.EnableEvents = True
    
35. End Sub
    
36. Private Sub ÅçÃÒB4()
    
37.     Dim Ar As String, xI As Integer
    
38.     With Sheets("¤H­û¸ê®Æ").[A2]
    
39.         xI = 2
    
40.         Do While .Cells(xI) <> ""
    
41.             If .Cells(xI) = [B3] Then
    
42.                 If Ar = "" Then
    
43.                     Ar = "," & .Cells(xI, 3)   '¤¤¤å©m¦W ¦b²Ä3Äæ
    
44.                 Else
    
45.                     If InStr(Ar, "," & .Cells(xI, 3) & ",") = 0 Then Ar = Ar & "," & .Cells(xI, 3)
    
46.                 End If
    
47.             End If
    
48.             xI = xI + 1
    
49.         Loop
    
50.     End With
    
51.     With Range("B4").Validation
    
52.         .Delete
    
53.         If Ar <> "" Then .Add xlValidateList, , , Mid(Ar, 2)
    
54.     End With
    
55. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 7# sheau-lan
­n¦p¦ó¬d¸ß??
¦^´_ 8# JEAN
5# ªþÀÉ [¤H­û¸ê®Æ] ½Ð¥ý«Ø¥ß¦W³æ¸ê®Æ...
6#ªºµ{¦¡½X ½Æ»sÂл\  5# [»â¥Î³æ]¤u§@ªíªºµ{¦¡½X
¨ì¬¡­¶Ã¯µøµ¡ ­¶­º  °õ¦æ»â¥Î³æ ¥ý¿ïB3,¦A¿ïB4 ¬Ý¬Ý

GBKEE

¦^´_ 10# JEAN
¨S¿ù µ{¦¡½X ¸m[»â¥Î³æ]¼Ò²Õ
¸ê®Æ¨S§¹¾ã

     

¤U¸ü (36.61 KB)

2012-10-22 12:22

PS ¦^¤å®É  ½Ð«ö [¦^ÂÐ] «ö¶s  µªÂЧAªº¤H¤~·|±o¨ì³qª¾, ³o¬O°ò¥»ªºÂ§»ª

GBKEE

¦^´_ 13# JEAN
6# ªºµ{¦¡½X ·§©À  B4ªºÅçÃÒ²M³æ : ¶È¬OB3¿ï¾Ü³¡ªùªº¤H­û,¨Ã«D¬O¥þ³¡ªº¤H­û²M³æ

·í¶i¤J[»â¥Î³æ]®É, ³]¥ß(³¡ªù) B3ªºÅçÃÒ²M³æ,¬° Sheets("¤H­û¸ê®Æ").[E2]....[E?] ªº½d³ò

[»â¥Î³æ] B3 ¦³§ïÅÜ-> ­«·s³]¥ß(¤H­û)  B4ªºÅçÃÒ²M³æ
¬° Sheets("¤H­û¸ê®Æ").[A:A] =B3(³¡ªù)½d³ò,Sheets("¤H­û¸ê®Æ").[C:C] ªº¦W³æ

GBKEE

¦^´_ 16# JEAN
¤H­û¦W³æ¶·¬O³sÄò¤£¥i¤¤Â_ªº

GBKEE

¦^´_ 18# JEAN
­×§ï©w¸q¦WºÙ¤½¦¡ :¤¤¤å©m¦W
=OFFSET(¤H­û¸ê®Æ!$B$3,0,0,COUNTA(¤H­û¸ê®Æ!$B$3:$B$65535),1)
[»â¥Î³æ] ¼Ò²Õ´N¤@µ{¦¡§Y¥i¦p¤U

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim xf As Range
   
3.     Application.EnableEvents = False
   
4.     If Target(1).Address(0, 0) = "B4" Then
   
5.         If Target(1) = "" Then
   
6.             Range("B3") = ""
   
7.         Else
   
8.             Set xf = [¤¤¤å©m¦W].Find(Target(1), lookat:=xlWhole)
   
9.             Range("B3") = xf.Offset(, -1)            
   
10.         End If
    
11.     End If
    
12.     Application.EnableEvents = True
    
13. End Sub

½Æ»s¥N½X