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

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

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

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

Book2.zip (5.47 KB)

¦^´_ 1# y663258


    §AªºÁ|¨ÒC16¬°¦ó¬O16?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-11-18 07:04 ½s¿è

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

TOP

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

TOP

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

TOP

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

TOP

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

ÁÂÁ¤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

TOP

        ÀR«ä¦Û¦b : «Î¼e¤£¦p¤ß¼e¡C
ªð¦^¦Cªí ¤W¤@¥DÃD