ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

½Ð°Ý¤@¤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
ÁÂÁÂ

µe­±.rar (65.39 KB)

Tony

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

TOP

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

TOP

¦^´_ 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¸Õ¸Õ¬Ý.

TOP

¦^´_ 4# GBKEE

µe­±.rar (83.45 KB)

Tony

TOP

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


REµe­±.rar (125.31 KB)

TOP

¥»©«³Ì«á¥Ñ tonycho33 ©ó 2012-1-16 11:30 ½s¿è

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

ÁÂÁÂ
Tony

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-1-16 13:42 ½s¿è

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


ª«¥ó¼Ò²Õµ{¦¡½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

TOP

¦^´_ 8# GBKEE

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

µe­±.rar (130.38 KB)

Tony

TOP

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

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD