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

[µo°Ý] ¥ý¤£­«½Æ¿z¿ï«á¡A¨Ã¦Û°Ê³]¬°¤U©Ô¦¡¿ï³æ

¶X³o­Óª©­±¸ß°Ý¤@¤U¡AWorkSheet_Changeªº¥Îªk
©³¤U¤À§O¬Oª©¥D¸òÀs¤jªºµ{¦¡½X
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim xF As Range, xA As Range
  3. With Target
  4.      If .Count > 1 Or .Columns.Count > 1 Or .Row < 2 Or .Column <> [I1].Column Then Exit Sub
  5.      Set xA = .Cells(1, -1).Resize(1, 2): xA.ClearContents
  6.      If .Value <> "" Then Set xF = [²M³æ!d:d].Find(.Value, Lookat:=xlWhole)
  7.      If Not xF Is Nothing Then xA = xF(1, -1).Resize(1, 2).Value
  8. End With
  9. End Sub



  10. Private Sub Worksheet_Change(ByVal Target As Range)

  11. If Target.Column <> 9 Then Exit Sub

  12. Application.EnableEvents = False
  13. ¶ë¸ê®Æ¤J¦r¨å
  14. Key$ = Target.Value
  15. Cells(Target.Row, 8) = ¤U©Ô¿ï³æ.D(Key).¥Î³~§O
  16. Cells(Target.Row, 7) = ¤U©Ô¿ï³æ.D(Key).¤u§@­pµe
  17. Application.EnableEvents = True

  18. End Sub
½Æ»s¥N½X
§Úªº°ÝÃD¬O
¤@¡B¬°¤°»òIFªº«á­±³£¤£»Ý­n¥[¤W END IF¦Ó¥B°£¿ù³£¨S°ÝÃD

¤G¡BTarget«üªº¬O·Æ¹«ÂI¨ìªº¥ô¦ó¤@­ÓÀx¦s®æ¶Ü?   ¦]¬°¥i¥H¨ú±oTarget.Row
       ©Î¬OTarget.Column¡C
      ¦ý¬OPrivate Sub Worksheet_Change(ByVal Target As Range)¤S§âTarget·íRANGE ?

¤T¡B§Ú´ú¸Õ¤F¤@­Ó¤pµ{¦¡¡A¦ý¬O¥u°Ê¤F¤@¦¸¡A´N¨S°Ê§@¤F¡A¤£¾å±o¿ù¦b­þ¸Ì
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Column <> 3 Then Exit Sub
  3. Application.EnableEvents = False
  4. Target.Interior.ColorIndex = 3
  5. End Sub
½Æ»s¥N½X
·Ó²z»¡¡A§ÚÀH«K¿ï¤@­ÓÀx¦s®æ¡A¦pªG¸ÓÀx¦s®æ¤£¬O²Ä3Äæ (CÄæ)
     ¨º´NÂ÷¶}µ{¦¡
     ¦pªG¿ï¨ì¨ä¥LÄæ¡A¨º´N·|§âÀx¦s®æÃC¦âÅܬõ¦â
     ¦ý¬O´N¨S¿ìªk
     ´Nºâ§Ú¿ï¨ìªºÀx¦s®æ¡AÀH«K¿é¤J123¡A¦A§R±¼¡A³o¼Ëºâ¬O¦³Change¤F§a?
     ¤]¨S°Ê§@....

¥|¡BSet xA = .Cells(1, -1).Resize(1, 2): xA.ClearContents ³oÃä.Resize¬O¤°»ò¥\¯à?

¥H¤W¸ß°Ý¡AÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

¦^´_ 21# iceandy6150


1)¡B¬°¤°»òIFªº«á­±³£¤£»Ý­n¥[¤W END IF¦Ó¥B°£¿ù³£¨S°ÝÃD?
__if ±ø¥ó then µ²ªG, ¼g¦¨¤@¦æ, ´N¤£¶·¥[ end if

2)¡BTarget«üªº¬O·Æ¹«ÂI¨ìªº¥ô¦ó¤@­ÓÀx¦s®æ¶Ü?
__´N¬Oselection¤¤ªºrangeª«¥ó, ¥i¥H¬O³æ®æ, ¿ïÄò½d³ò, ©Î¤£³W«h¿ï¨ú°Ï¶ô

3)¡B§Ú´ú¸Õ¤F¤@­Ó¤pµ{¦¡¡A¦ý¬O¥u°Ê¤F¤@¦¸¡A´N¨S°Ê§@¤F¡A¤£¾å±o¿ù¦b­þ¸Ì?
__Application.EnableEvents = False
  "IJµo¨Æ¥ó"--Ãö³¬, ³o¬O¬°¤FÁקK"­«´_IJµo"³y¦¨µL­­´`Àô,
  µ{¦¡©³¤UÀ³¦A¦h¤@¦æ:
  Application.EnableEvents = True
  («ì´_IJµo) §_«h¤U¦¸´N¨S§@¥Î!

4)¡BSet xA = .Cells(1, -1).Resize(1, 2) ³oÃä.Resize¬O¤°»ò¥\¯à?
    ¥H¤@­Órange©wÂI, ¦V¥k©Î¦V¤U"ÂX®i"¦¨¤@­Ó½d³ò,
  ¨Ò¦p: range("a1").resize(4,3)  ---> A4:C4


=============================

TOP

¦^´_ 22# ­ã´£³¡ªL


    ·PÁª©¥Dªº±Ð¾Ç¦^ÂСA§Ú¦A¨Ó¦n¦n¸Õ¸Õ³o´X­Ó¥\¯à¡A·PÁÂ
«¢Åo~¤j®a¦n§r

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD