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

[µo°Ý] ½Ð°Ýªu¥Îªí®æ,­Y±NA1¸ê®Æ§R°£,B1¤ÎC1...¥H«áªºÀx¦s®æ¸ê®Æ¬O§_¥i¥H ¸òµÛ®ø¥¢

[µo°Ý] ½Ð°Ýªu¥Îªí®æ,­Y±NA1¸ê®Æ§R°£,B1¤ÎC1...¥H«áªºÀx¦s®æ¸ê®Æ¬O§_¥i¥H ¸òµÛ®ø¥¢

:dizzy:
½Ð°Ý­Y§Úªu¥Îªí®æ¨Ókey ¤J·s¸ê®Æ
¦Ó§Ú­ì¦³ªí®æ¤¤,Àx¦s®æ³£¬O¦³¸ê®ÆÅçÃÒªº
·í§Ú±NA1ªí®æªº¸ê®Æ²M°£®É,B1,C1....«á­±Àx¦s®æ¤¤ªº¸ê®Æ¨ÌµM¦s¦b
§Ú·Qª¾¹D¥i¤£¥i¯à·í§ÚA1Àx¦s®æ¤¤¸ê®Æ²M°£«á,«á­±ªºÀx¦s®æ¤]¦Û°Ê¸òµÛ²M°£
ÁÂÁ¦U¦ì¥ý¶i

¥»©«³Ì«á¥Ñ mistery ©ó 2010-12-20 17:48 ½s¿è

¦^´_ 1# ClareWu


    A1 B1 C1  ¤TªÌ¦³³s°ÊÃö«Y¶Ü?? ÁÙ¬O¦³¹ê¨Ò¥i°Ñ¦Ò?
   (·PıÀ³¸Ó¬O¥ÎVBA¼g....)

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2010-12-21 11:14): ½Ð¤W¶ÇÀÉ®×»¡©ú

¦^´_ 2# mistery


    A1,B1,C1¬O¦h¼h¦¸¿z¿ï
¤]´N¬O»¡,B1¬O®Ú¾ÚA1¦Ó¨Óªº¿z¿ï²M³æ
C1¬O¨ÌA1,B1¦Ó¨Óªº
¦Ó·í§Ú§âA1³o­Ó®æ¤l¤º®e²M°£,B1¤ÎC1ªº¤º®eÁÙ¬O¦s¦b

TOP

¦^´_ 2# mistery


    ¦a§}.zip (26.63 KB)
¦pªþ¥ó¦a§}³o­ÓÀÉ®×,­Y§Ú²M°£¿¤¥«³o­ÓÀx¦s®æ,«á­±ªº¶mÂí¤Î§ø¨½¸ê®ÆÁÙ¬O¦s¦b

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2010-12-21 15:17): ¤W¶ÇÀ£ÁYÀɽФŨϥαK½X

¦^´_ 4# ClareWu


    ²M³æªº³¡¥÷  ¥i¥Î¡@indirect() ¨ç¼Æ
    °µ¤F¤@­Ó¤p¤p½d¨Ò  (¤£¹L§Ú¬O¥Î¤â°Ê³v¤@©R¦W²M³æ)

    ·íA1§ïÅܮɠ ¦Û°Ê²M°£ B1 C1  ´N­n¥Î¨ì VBA Åo (Worksheet_SelectionChange )
   

    ¦a§}.zip (46.63 KB)

TOP

§Ú¤£¯à¶}±zªºªþÀÉ,¤Óµæ¤F
¥Îindirect¸ò¥Îoffset¥h©R¦W²M³æ¦³«Ü¤j®t²§¶Ü?
¦]¬°§Ú¥Îoffset¥h©R¦W²M³æ¤]¬O­n¤â°Ê³v¤@©R¦W

TOP

¦^´_ 6# ClareWu
²MªÅªº°ÝÃD¥Îvba¸Ñ¨M
sheet1¼Ò²Õ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Count > 1 Then Exit Sub
  3. Application.EnableEvents = False
  4. If Target.Column = 1 And Target = "" Then Target.EntireRow = "" '¾ã¦C²MªÅ
  5. Application.EnableEvents = True
  6. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh

­ÉÃD...½Ð°Ý Hsieh ª©¥D ...¸Ó«ç¼Ë¥ÎVBA ¥h§Ö³t©R¦W²M³æ?(¥H¦¹®×¬°¨Ò)

TOP

¥»©«³Ì«á¥Ñ mistery ©ó 2010-12-21 16:44 ½s¿è

¦^´_ ClareWu


     (§ó·sªþ¥ó    ¤§«eªº¤£¤p¤ß¥[±K¤F  ¦A§â Hsieh ª©¥D¼gªº¦Û°Ê²M°£ VBA ¥[¶i¥h)

      ¦a§}.zip (48.81 KB)

TOP

¦^´_ 8# mistery
¤£«Øij³o¼Ë°µªk
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim A As Range, B As Range, Rng As Range, MyStr$
  3. If Target.Count > 1 Then Exit Sub
  4. Application.EnableEvents = False
  5. If Target.Column = 1 And Target = "" Then
  6. Target.EntireRow = "" '¾ã¦C²MªÅ
  7. ElseIf Target.Column = 1 And Target <> "" Then
  8. Set A = Sheet2.Columns("A").Find(Target, lookat:=xlWhole)
  9. k = Application.CountA(A.EntireRow)
  10. MyStr = Join(Application.Transpose(Application.Transpose(A.Offset(, 1).Resize(, k - 1))), ",")
  11. With Target.Offset(, 1).Validation
  12. .Modify 3, , , MyStr
  13. End With
  14. ElseIf Target.Column = 2 And Target <> "" Then
  15. Set A = Sheet3.Columns("A").Find(Target.Offset(, -1), lookat:=xlWhole)
  16. Set B = Sheet3.Columns("A:B").Find(Target, after:=A, lookat:=xlWhole)
  17. Set Rng = Sheet3.Range(B.Offset(, 1), B.End(xlToRight))
  18. If Rng.Count = 1 Then MyStr = Rng Else MyStr = Join(Application.Transpose(Application.Transpose(Rng)), ",")
  19. With Target.Offset(, 1).Validation
  20. .Modify 3, , , MyStr
  21. End With
  22. End If
  23. Application.EnableEvents = True
  24. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD