¥ô¿ïÀx¦s®æ²Ö¥[³]©w¼Æ­È

y663258

¦U¦ì¤j¤j:¦pªþ¥ó»¡©ú¦Û¤v§ä¸ê®Æ´N¬O´ê¤£¥Xµ{¦¡¡A½Ð¨ó§UÁÂÁ¡C

Hsieh

¦^´_ 1# y663258


    §AªºÁ|¨ÒC16¬°¦ó¬O16?

GBKEE

¦^´_ 1# y663258

1. Option Explicit
   
2. Public A As Integer
   
3. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
4.     Dim AR(1 To 7) As Range, i As Integer, s As Integer
   
5.     Application.EnableEvents = False
   
6.     Set AR(1) = [A3:E12]
   
7.     Set AR(2) = [G3:K12]
   
8.     Set AR(3) = [M3:Q12]
   
9.     Set AR(4) = [A15:E24]
   
10.     Set AR(5) = [G15:K24]
    
11.     Set AR(6) = [M15:P24]
    
12.     Set AR(7) = [A27:D37]
    
13.     For i = 1 To 7
    
14.         If i = 1 Then s = 1 Else s = s * 2
    
15.         If Not Intersect(Target(1), AR(i)) Is Nothing Then            A = s + A
    
16.     Next
    
17.     Application.EnableEvents = True
    
18. End Sub
    
19. Sub Ex()   '´¡¤Jª«¥ó(¹Ï¤ù,¤å¦r®Øµ¥..«ö¶s) «ü©w¦¹¥¨¶°
    
20.     Dim Rng As Range
    
21.     Set Rng = Range("B39", Range("B39").End(xlDown))
    
22.     Rng(A).Select
    
23.     MsgBox Rng(A)
    
24.     A = 0
    
25. End Sub

½Æ»s¥N½X

y663258

Hsieh ¤j¤jC16¬O¦b4À³¬O8¡A»~­È¤F¤£¦n·N«ä¡C

y663258

GBKEE ¤j¤j¤jÁÂÁÂÀ°¦£         
    Dim Rng As Range
    Set Rng = Range("B39", Range("B39").End(xlDown))
    Rng(A).Select
    MsgBox Rng(A)
    A = 0
End Sub

¸g´ú¸Õ¥uÅã¥Ü(©m¤ó)¤£¯à¤ñ¹ï²Ö¥[«á¼Æ­ÈÅã¥Ü¥X©m¤ó¡A½Ð¦A­×¥¿ÁÂÁ¡C

GBKEE

¦^´_ 5# y663258
'Module ªºµ{¦¡½X

1. Option Explicit
   
2. Public A()     'Module ªºµ{¦¡½X
   
3. Sub Ex()   '´¡¤Jª«¥ó(¹Ï¤ù,¤å¦r®Øµ¥..«ö¶s) «ü©w¦¹¥¨¶°
   
4.     Dim Rng As Range, M As String, i
   
5.      Set Rng = Range("B39", Range("B39").End(xlDown))
   
6.     On Error GoTo Thend
   
7.     For i = 0 To UBound(A) - 1
   
8.         M = M & IIf(M <> "", " : ", "") & Rng(A(i))
   
9.     Next
   
10.     MsgBox M
    
11.     Erase A
    
12. Thend:
    
13. End Sub

½Æ»s¥N½X

Worksheet ªºµ{¦¡½X

1. Option Explicit
   
2. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
3.     Dim AR(1 To 7) As Range, i As Integer, s As Integer
   
4.     Application.EnableEvents = False
   
5.     Set AR(1) = [A3:E12]
   
6.     Set AR(2) = [G3:K12]
   
7.     Set AR(3) = [M3:Q12]
   
8.     Set AR(4) = [A15:E24]
   
9.     Set AR(5) = [G15:K24]
   
10.     Set AR(6) = [M15:P24]
    
11.     Set AR(7) = [A27:D37]
    
12.     For i = 1 To 7
    
13.         If i = 1 Then s = 1 Else s = s * 2
    
14.         If Not Intersect(Target(1), AR(i)) Is Nothing Then
    
15.             On Error GoTo TEN:
    
16.             A(UBound(A)) = s
    
17.             ReDim Preserve A(UBound(A) + 1)
    
18.         End If
    
19.     Next
    
20.     Application.EnableEvents = True
    
21.     Exit Sub
    
22. TEN:
    
23. ReDim A(0)
    
24. Resume
    
25. End Sub

½Æ»s¥N½X

y663258

GBKEE ¤j¤jÁÂÁ«ü¾É¨Ì¥Ü´ú¸ÕMsgBoxÅã¥Ü¬OÂI¿ï¹Lªº©m¤ó,¥i§_Åã¥ÜÂI¿ï¹L²Ö¥[ªº©m¤ó¡A
    ¦pÂI¿ïb4 ,h4,n4,g22=1+2+4+16=23¤ñ¹ïb39:b138MsgBoxÅãµM§º¡C

Hsieh

¦^´_ 7# y663258

1. Sub nn()
   
2. Dim ar As Range
   
3. Set ar = Union([A3:E12], [G3:K12], [M3:Q12], [A15:E24], [G15:K24], [M15:P24], [A27:D37])
   
4. For Each a In Selection
   
5. s = 1
   
6. For Each c In ar.Areas
   
7.   Set b = Intersect(a, c)
   
8.   If Not b Is Nothing Then cnt = cnt + s: Exit For
   
9.   s = s * 2
   
10. Next
    
11. Next
    
12. MsgBox [B39:B138].Cells(cnt, 1)
    
13. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 7# y663258
½Ð¬Ý    3¼Ó¤w­×¥¿ªºµ{¦¡½X.

y663258

ÁÂÁ¤G¦ì¤j¤j¤£¹½¨ä·Ð¼·¤¾«ü±Ð¡AHsieh µ{¦¡¸g´ú¸Õ¡AMsgBox Åã¥Üªº¬O¡A¿ï¨ú¹Lªº³Ì«á¤@²Õ¡A¤p§Ì­nªº¬O¿ï¨ú¹Lªº²Ö¥[¦X­p¼Æ­È©Ò¹ïÀ³©m¤ó¡C¦pb4 ,h4,n4,g22=1+2+4+16=23¤ñ¹ïb39:b138MsgBoxÅãµM§º¡A³o¬O²q©m¤óªº¹CÀ¸¡C¦b1-7½Ð³Q²qªÌ§i¶D²q´úªÌ1-7¦³¥X²{©m¤óªº¡A¦p1¡A2¡A3¡A5¦³¥X²{´N¬O1+2+4+16=23¹ï·Ó©m¤óªí§º¡A³Q²qªÌ´N¬O©m§º¡A¥S¥x¦AÀ°¦£­×¥¿¡C