½Ð°Ý¤@­ÓVBA «ö¶s³sµ²°ÝÃD

tonycho33

½Ð°Ý¤@¤U§Ú·Q³]­p¤W­±¦³«ö¶sA1~A25
¹ïÀ³ªº¸ê®Æ®w¦b¥ªÃä(ABÄ欰ªÅ¥Õ)
¥u­n«ö¤U¹ïÀ³ªº«ö¶s«hÅã¥Ü¦bK3~Q7ªºÀx¦s®æ¤W

¥t¥~,M6¬O¶}©l®É¶¡,M7¬Oµ²§ô®É¶¡,S1¬O¤ñ¹ï®É¶¡­È
¦pªG¤ñ¹ï®É¶¡­È¦bM6~M7(¹ïÀ³¨ì¸ê®Æ®w¤Wªº¤ñ¹ï®É¶¡)¤§¶¡«hÅã¥Ü¹ïÀ³ª«¥óºñ¦â
¦pªG¶W¥X°Ï¶¡«hÅã¥Ü¬õ¦â

­n¦p¦ó¼g©O
ÁÂÁÂ

tonycho33

½Ð¨D¦U¦ì¨ó§U
ÁÂÁÂ

register313

¦^´_ 2# tonycho33

±N«ö¶s§ï¬°Àx¦s®æ
¥ý¥u³B²zA1~A11

1. Private Sub Worksheet_SelectionChange(ByVal T As Range)
   
2. If T.Row = 27 And T.Column >= 7 And T.Column <= 17 Then
   
3. Set Rng = Columns("Z").Find(T)
   
4. [K3].Resize(5, 7) = Rng.Resize(5, 7).Value
   
5. If ([S1] > [N6]) And ([S1] < [N7]) Then
   
6.    Shapes("Group " & Mid(Rng.Value, 2, 2)).Fill.ForeColor.SchemeColor = 11
   
7. Else
   
8.    Shapes("Group " & Mid(Rng.Value, 2, 2)).Fill.ForeColor.SchemeColor = 45
   
9. End If
   
10. End If
    
11. End Sub

½Æ»s¥N½X

¥ý抛¥X¤@ÂI·Qªk,«áÄòÅý¤j¤j­ÌÄ~Äò°Q½×
µe­±.rar (68.08 KB)

¤U¸ü¦¸¼Æ: 5

2012-1-13 13:07

GBKEE

¦^´_ 2# tonycho33
§AÀɮתº «ö¶sA1~A25 ¬O : ¤u¨ã->¦Û­q-ªí³æ               ªº«ö¶s(Button)
                                ½Ð§ï¦¨:  ¤u¨ã->¦Û­q-±±¨î¤u¨ã½c   ªº«ö¶s(CommandButton)
¹ïÀ³ª«¥ó ªº©R¦W (¦³¹ïÀ³©Ê) :  ¦p«ö¶s¤å¦r (A1)  -> ¹ïÀ³ª«¥ó ( Group 1)  ; «ö¶s¤å¦r( A16) ->  ¹ïÀ³ª«¥ó(Group 16)
½Ð­×§ï«á¤W¶Ç ¦A¸Õ¸Õ¬Ý.

tonycho33

¦^´_ 4# GBKEE

GBKEE

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


REµe­±.rar (125.31 KB)

¤U¸ü¦¸¼Æ: 15

2012-1-13 18:43

tonycho33

¦^´_ 6# GBKEE

§A¦n
§A¼gªºµ{¦¡¥i¥HRUN
¦ý¬O¦³°ÝÃD·Q½Ð±Ð
¸ê®Æ®w¤¤ZÄæ¨ìAFÄ檺Àx¦s®æ¦pªG¦³¤½¦¡³sµ²ªº¸Üªº¸Ü¦n¹³´N·|§ä¤£¨ì
­n¦p¦ó³B²z©O
­n¦p¦ó·s¼W
®ÇÃ䪺B1¡ãB8
             C1¡ãC3
             D1¡ãD2

ÁÂÁÂ

GBKEE

¦^´_ 7# tonycho33
¸ê®Æ®w¤¤ZÄæ¨ìAFÄ檺Àx¦s®æ¦pªG¦³¤½¦¡³sµ²ªº¸Üªº¸Ü¦n¹³´N·|§ä¤£¨ì  ½ÐÁ|­Ó¨Ò
­n¦p¦ó·s¼W®ÇÃ䪺B1¡ãB8             C1¡ãC3             D1¡ãD2                       ¥i¨Ì¼Ëµe¸¬¯ø  
½Ð§ï¦¨:  ¤u¨ã->¦Û­q-±±¨î¤u¨ã½c   ªº«ö¶s(CommandButton)     ¹ïÀ³ªº¹Ï¤ù ½Ð­n¥ý¥[¤u   ¦p¹Ï:

¤U¸ü (24.87 KB)

2012-1-16 13:29

ª«¥ó¼Ò²Õµ{¦¡½X­×§ï¦p¤U

1. Option Explicit
   
2. Public WithEvents A_CommandButton As MSForms.CommandButton   'ª«¥ó¼Ò²Õªºª«¥óÅܼÆ
   
3. Private Sub A_CommandButton_Click()
   
4.     Dim S As String, Rng As Range
   
5.     S = A_CommandButton.Caption
   
6.     With Sheet21
   
7.         Set Rng = .Columns("Z").Find(S, LookAT:=xlWhole)
   
8.         If Rng Is Nothing Then MsgBox "§ä¤£¨ì " & S: Exit Sub
   
9.         [K3].Resize(5, 7) = Rng.Resize(5, 7).Value
   
10.         If S Like "A*" Then
    
11.             S = Replace(A_CommandButton.Caption, "A", "Group ")
    
12.             '"A" §ï¬°"Group " :  A15 ->   Group 15
    
13.         ElseIf S Like "B*" Then
    
14.             S = Replace(A_CommandButton.Caption, "A", "GroupB ")
    
15.             '"B" §ï¬°"GroupB " :  B1 ->   GroupB 1
    
16.         ElseIf S Like "C*" Then
    
17.             S = Replace(A_CommandButton.Caption, "C", "GroupC ")
    
18.             '"C" §ï¬°"GroupC " :  C1 ->   GroupC 1
    
19.         ElseIf S Like "D*" Then
    
20.             S = Replace(A_CommandButton.Caption, "D", "GroupD ")
    
21.             '"B" §ï¬°"GroupD " :  D1 ->   GroupD 1
    
22.         End If
    
23.         .Shapes(S).Select
    
24.         If .[S1] > .[N6] And .[S1] < .[N7] Then
    
25.             Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
    
26.         Else
    
27.             Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
    
28.         End If
    
29.     End With
    
30.     DoEvents
    
31.     ActiveCell.Select
    
32. End Sub

½Æ»s¥N½X

tonycho33

¦^´_ 8# GBKEE

¤½¦¡³sµ²ªº°ÝÃD

GBKEE

¦^´_ 9# tonycho33
ª«¥ó¼Ò²Õªºµ{¦¡½X
Set Rng = .Columns("Z").Find(S,  LookAt:=xlWhole)
§ï¦¨
Set Rng = .Columns("Z").Find(S, LookIn:=xlValues, LookAt:=xlWhole)