GBKEE

¦^´_ 9# §±æ¢
2# Hsieh ¦³»¡

1. ¦^´_ 1# §±æ¢
   
2. ¥X³f³æ¼Ò²Õ

½Æ»s¥N½X

¤U¸ü (82.89 KB)

2014-4-15 10:42

GBKEE

¦^´_ 12# yen956

1. ¦]¬° sub xxxx(ByVal Target As Range) ¦³°Ñ¼Æ¬OµLªk«ü©w¥¨¶°ªº,

½Æ»s¥N½X

³o¤£¹ïªº

¸Õ¸Õ¬Ý sheet1ªº

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     MsgBox Target.Address
   
3. End Sub

½Æ»s¥N½X

¨ä¥L¼Ò²Õ¤¤

1. Sub Ex()
   
2.     Run "sheet1.Worksheet_SelectionChange", Range("A15")
   
3. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 14# §±æ¢
¬O­n§ï¦¨¦p¦¹¶Ü?

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim AR(), s%, T As Integer
   
3.     Application.EnableEvents = False
   
4.     If Target.Address = "$H$5" Then
   
5.         T = 6        '³]©wÄæ¼Æ  *********
   
6.         With Sheets("¥X³f²M³æ")
   
7.             For Each a In .Range(.[L3], .[L3].End(xlDown))
   
8.                 If a = Target Then
   
9.                     m = a.Offset(, -2).Value
   
10.                     ReDim Preserve AR(s)
    
11.                     AR(s) = Application.Transpose(Application.Transpose(a.Offset(, -11).Resize(, T).Value))
    
12.                     'Ar(s) = Application.Transpose(Application.Transpose(a.Offset(, -11).Resize(, 8).Value))
    
13.                     s = s + 1
    
14.                 End If
    
15.             Next
    
16.         End With
    
17.         Range([A9].Resize(, T), [A9].Resize(, T).End(xlDown)).ClearContents
    
18.         'Range([A9].Resize(, 8), [A9].Resize(, 8).End(xlDown)).ClearContents
    
19.         [C3] = m
    
20.         [A9].Resize(s, T) = Application.Transpose(Application.Transpose(AR))
    
21.         '[A9].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
    
22.     End If
    
23.     Application.EnableEvents = True
    
24. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 17# yen956
Âà­ÓÅs¤]¥i¥H

1. Sub Test()
   
2.     Test1 Range("H5")
   
3. End Sub
   
4. Sub Test1(ByVal Target As Range)
   
5.     MsgBox Target.Address
   
6. End Sub

½Æ»s¥N½X

¦^´_ 18# §±æ¢
­×§ï¤@¤U,¸Õ¸Õ¬Ý

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim Ar(), s%
   
3.     Application.EnableEvents = False
   
4.     If Target.Address = "$H$5" Then
   
5.         With Sheets("¥X³f²M³æ")
   
6.             For Each a In .Range(.[L3], .[L3].End(xlDown))
   
7.                 If a = Target Then
   
8.                     m = a.Offset(, -2).Value
   
9.                     ReDim Preserve Ar(s)
   
10.                     Ar(s) = Application.Transpose(Application.Transpose(a.Offset(, -11).Resize(, 8).Value))
    
11.                     s = s + 1
    
12.                 End If
    
13.             Next
    
14.         End With
    
15.         If s > 0 Then
    
16.             [C3] = m
    
17.             With [A9:H24]
    
18.                 .Cells = ""
    
19.                 .Cells(1).Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
    
20.             End With
    
21.         Else
    
22.             MsgBox "¥X³f²M³æ ¤¤¨S¦³ " & Target
    
23.         End If
    
24.     End If
    
25.     Application.EnableEvents = True
    
26. End Sub

½Æ»s¥N½X