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

½Ð±Ðµ{¦¡³s°Ê°ÝÃD

½Ð±Ðµ{¦¡³s°Ê°ÝÃD

¦U¦ì¥ý¶i¦n¡A¤p§Ì³Ìªñ¸ÕµÛ¥ÎYESWIN°µ¤@­ÓDDE³sµ²ªí¦pªþ¥ó
·Q½Ð°Ý¤@¤U¡A¸Ó¦p¦ó¥h­×§ï»yªk¤º®e¡A¤~¯àÅý§Ú¦bÅܧó¤u§@ªí²Ä¤@¦CªºÄæ¦ì¼ÐÃDªº®É­Ô¡A
¤U­±ªº¤º®e¤]¥i¥H¸òµÛ°µ³s°ÊÅܤƩO¡H
°²³]§Ú¥»¨ÓBÄæ¼ÐÃD¬OªÑ¦W¡A§Úª½±µ§â"ªÑ¦W"§ï¦¨"Ãþ§O"¡A
¨º»ò¡uTarget.Offset(0, 1) = "=YES|DQ!'" & Target & ªÑ¦W ¡v³o¤@¥y¡A
´N·|¦Û¤vÅܦ¨¡uTarget.Offset(0, 1) = "=YES|DQ!'" & Target & Ãþ§O ¡v¡A¤u§@ªí¸ÌBÄæ´N¯à¥D°Ê±a¥X¸ÓªÑªºÃþ§O¡A
·Ð½Ð¦U¦ì°ª¤â¤£§[½ç±Ð¡AÁÂÁÂ~
µ{¦¡¥N½X¦p¤U
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim ªÑ¦W, ¦¨¥æ»ù, ¶}½L, °ª, §C, º¦°±, ¶^°±, º¦¶^, Ãþ§O As String
  3. ªÑ¦W = ".Name'"
  4. ¦¨¥æ»ù = ".Price'"
  5. ¶}½L = ".Open'"
  6. °ª = ".High'"
  7. §C = ".Low'"
  8. º¦°± = ".Ceil'"
  9. ¶^°± = ".Floor'"
  10. º¦¶^ = ".Change'"
  11. Ãþ§O = ".GroupName'"
  12. If Target.Column = 1 And Target.Row > 1 Then
  13.     On Error Resume Next
  14.     If Target <> "" Then
  15.     Target.Offset(0, 1) = "=YES|DQ!'" & Target & ªÑ¦W
  16.     Target.Offset(0, 2) = "=YES|DQ!'" & Target & ¦¨¥æ»ù
  17.     Target.Offset(0, 3) = "=YES|DQ!'" & Target & ¶}½L
  18.     Target.Offset(0, 4) = "=YES|DQ!'" & Target & °ª
  19.     Target.Offset(0, 5) = "=YES|DQ!'" & Target & §C
  20.     Target.Offset(0, 6) = "=YES|DQ!'" & Target & º¦°±
  21.     Target.Offset(0, 7) = "=YES|DQ!'" & Target & ¶^°±
  22.     Else: Target.Offset(0, 1).Resize(1, 7) = ""
  23.     End If
  24. End If
  25. End Sub
½Æ»s¥N½X

Book1.rar (15.91 KB)

¡uTarget.Offset(0, 1) = "=YES|DQ!'" & Target & ªÑ¦W ¡v³o¤@¥y¡A
´N·|¦Û¤vÅܦ¨¡uTarget.Offset(0, 1) = "=YES|DQ!'" & Target & Ãþ§O ¡v¡A¤u§@ªí¸ÌBÄæ´N¯à¥D°Ê±a¥X¸ÓªÑªºÃþ§Oowen06 µoªí©ó 2014/1/21 00:02

¦³¦ó°ÝÃD,ª½±µ§ï¤@¤U¤£´N¦¨¤F.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Set d = CreateObject("Scripting.Dictionary")
  3. d("ªÑ¦W") = ".Name'"
  4. d("¦¨¥æ»ù") = ".Price'"
  5. d("¶}½L") = ".Open'"
  6. d("°ª") = ".High'"
  7. d("§C") = ".Low'"
  8. d("º¦°±") = ".Ceil'"
  9. d("¶^°±") = ".Floor'"
  10. d("º¦¶^") = ".Change'"
  11. d("Ãþ§O") = ".GroupName'"
  12. If Target.Column = 1 And Target.Row > 1 Then
  13.     On Error Resume Next
  14.     If Target <> "" Then
  15.     Target.Offset(0, 1) = "=YES|DQ!'" & Target & d([B1].Value)
  16.     Target.Offset(0, 2) = "=YES|DQ!'" & Target & d([C1].Value)
  17.     Target.Offset(0, 3) = "=YES|DQ!'" & Target & d([D1].Value)
  18.     Target.Offset(0, 4) = "=YES|DQ!'" & Target & d([E1].Value)
  19.     Target.Offset(0, 5) = "=YES|DQ!'" & Target & d([F1].Value)
  20.     Target.Offset(0, 6) = "=YES|DQ!'" & Target & d([G1].Value)
  21.     Target.Offset(0, 7) = "=YES|DQ!'" & Target & d([H1].Value)
  22.     Else: Target.Offset(0, 1).Resize(1, 7) = ""
  23.     End If
  24. End If
  25. End Sub
½Æ»s¥N½X
¦^´_ 1# owen06


    ³o­Ó·N«ä¶Ü?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ owen06 ©ó 2014-1-21 12:32 ½s¿è

¦^´_ 3# Hsieh


®@~¬Oªº¡A´N¬OHsiehª©¥D©Ò¸Ñµªªº³o¼Ë
¦]¬°§Ú¼gTarget.Offset(0, 1) = "=YES|DQ!'" & Target & [B1].Value ¤@ª½¨S¿ìªk§@¥Î
­ì¨Ó¬O¥~­±¦A¥]¤@¼h´N¦æ¤F¡Gd([B1].Value)
ÁÂÁª©¥D¤j¤j¡A·P¿E¤£ºÉ~~~

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD