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

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

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

¤j®a¦n¡A¤p§Ì¥Ø«e¦³¤@¤u§@ªí­n¶ñ¼g¡A¦p¹Ï1
1-1.jpg
2020-5-16 00:03

¤]´N¬O¤u§@ªí1ªº²ÄG¡BH¡BIÄæ
¦Ó¶ñ¼g¤º®e¥²¶·°Ñ·Ó¤u§@ªí2ªºªí®æ¡A¦p¹Ï2
2-1.jpg
2020-5-16 00:06


¦pªG[¥Î³~Ãþ§O(¬ì¥Ø)]¬O--ª««~/®ø¯Ó«~
«h[¥Î³~§O] ¥²©w¬O--·~°È¶O
¥B[¤u§@­pµe] ¤]¥²©w¬O--¤½¥q·~°È

¨ä¹ê·Ç«h¦b[¥Î³~Ãþ§O(¬ì¥Ø)]

§Úªº·Qªk¬O
¬JµM¤u§@ªí1ªº²ÄG¡BH¡BIÄæ¡A­nºCºC¥´¦r¡A¯à§_³]©w¤U©Ô¦¡¿ï³æ
¦Ó¿ï³æªº¤º®e­È¡A¬O±q¤u§@ªí2¥h¤£­«½Æ¿z¿ï
³o¼Ë´N¥i¥H¥Î¤U©Ô¦¡¥h¿ï¡A¤£¥²¥´¦r
¤£­«½Æ¿z¿ï«á¦p¹Ï3
3.JPG
2020-5-16 00:13


·í§Ú¦b¤u§@ªí1«ö¤U«ö¶s«á
·|¦Û°Ê§â¤u§@ªí1ªºGÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºF
·|¦Û°Ê§â¤u§@ªí1ªºHÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºG
·|¦Û°Ê§â¤u§@ªí1ªºIÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºH

¦ý¬O¡A¤u§@ªí2ªº³o­Óªí®æ¡A¬OÀH®É¦³¥i¯à¼W´îªº
¨C¤@¦¸°õ¦æ¡A³£­n¯à­«·s§PÂ_¡A¨Ã§âªº¿z¿ïµ²ªG²¾°£



¡i²Ä¤G­Ó¦Û°Ê¤Æ§¹¬ü°µªk¡j
´N¬O¬JµM·Ç«h¦b[¥Î³~Ãþ§O(¬ì¥Ø)] --(¤u§@ªí1ªºIÄæ)
¨º«ö¤U«ö¶s«á
·|¦Û°Ê§â¤u§@ªí1ªºIÄæ¡A³]©w¤U©Ô¦¡²M³æ¡A¤@¦¸50¦C¡A¨Ó·½¬O¤u§@ªí2ªºH
µM«áIÄæ¤U©Ô¿ï©w¬Y­Ó­È«á
·|¦Û°Ê§â¤u§@ªí1ªºHÄæ¡A¶ñ¤J¤u§@ªí2¹ï·Óªí®æ¤¤¬Û¹ïÀ³ªº­È
·|¦Û°Ê§â¤u§@ªí1ªºGÄæ¡A¶ñ¤J¤u§@ªí2¹ï·Óªí®æ¤¤¬Û¹ïÀ³ªº­È
(³s°Êªº·§©À)


¤£½×°µªk¤@ ©Î °µªk¤G(§¹¬üªk)
§Ú¥Î¿ý»s¥¨¶°ªº¤è¦¡§ä¥X ¤£­«½Æ¶i¶¥¿z¿ï¨Ã½Æ»s¶K¤W¨ì§Oªº¦a¤è ªº¼gªk
¥i¬O­nÅý¨º­Ó½d³ò¡A³]©w¬°¤U©Ô¦¡¿ï³æªº°Ñ·Ó½d³ò¡A¤×¨ä¥i¯à¤S¬O°ÊºAªº
§Ú¤@ª½·d¤£©w
¨D§U¦U¦ì¤j¤j¤F¡A·PÁÂ

ªþ¤WÀÉ®×
¦Û°Ê³]¤U©Ô¦¡¿ï³æ.rar (23.9 KB)
«¢Åo~¤j®a¦n§r

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


    ·PÁª©¥Dªº±Ð¾Ç¦^ÂСA§Ú¦A¨Ó¦n¦n¸Õ¸Õ³o´X­Ó¥\¯à¡A·PÁÂ
«¢Å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

¶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

¦^´_ 15# jackyq


    ¤j¤j¡A±z»¡ªº¥\¯à§Ú³£¤£·|¨Ï¥Î¡A¦pªG¦³¾÷·|¡A¦A½Ð±z±Ð¾Ç¤@¤U¡AÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

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


    ·PÁª©¥D¦^ÂбоǡA¬Ý¨Ó¤]¬O§Q¥ÎSheet_Change¥h°µ³B²z
    ³oÃä§Ú¦A¬ã¨s¤@¤U¨Ï¥Î¤è¦¡¡AÁÂÁÂ
«¢Åo~¤j®a¦n§r

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-5-22 15:42 ½s¿è

¦^´_ 13# iceandy6150


¤ñ¦p¡AA¿ï¨ì3¤jÃþ¡AB¿ï3¤¤Ãþ¡AC«o¿ï¥h1¤pÃþ---(³o¼Ë±bªº¬ì¥Ø´N¿ù¤F)  
_2¼ÓªºÀÉ®×, ¤£·|¦³³o°ÝÃD
_¥¼¿ï¨ú²Ä¤@¼h, ¤£·|­ã³\¿ï²Ä¤G¼h; ¿ï¤F²Ä¤@¼h, ¦Û°ÊÀ°§A¿z¿ï¥X²Å¦X²Ä¤@¼hªº²M³æ,
Äò¿ï²Ä¤G¼h(­Y²Ä¤G¼hªÅ¥Õ, ¥i¤£¥Î¿ï)«á, ¦A¹LÃy¥X²Ä¤T¼h, ¥i¥H»¡¤U©Ô²M³æ·|¶V¨Ó¶V¤Ö,
µ´¹ï¤£·|¦³¿ù¿ïªº°ÝÃD~~
_¦Ü¤_"²M³æ"¨Ó·½ªí, ¥i¤£­­¨î©¹¤U·s¼W, ¥u­n±N¤½¦¡¤U©Ô¨ì©³, ·|¦Û°Ê§PÂ_²M³æ½d³ò, ³o³¡¥÷ÁÙ¤£¥ÎVBA

¤u§@ªí¤@¥Î¨ìªºVBA:
1) ²MªÅ¿é¤J¸ê®Æ(­Y¤w±N­ì¦³¤º®e¶K¦Ü§O³B), ¨Ã­«¸m¤U©Ô²M³æ50¦æ

¦pªG²Ä¤T¼h²M³æ¤£­«ÂÐ(ªþÀɤ¤¦³¨â­Ó"¹q¶O", ­×§ï)
Xl0000170(¤T¼h¤U©Ô²M³æ)V2.rar (18.12 KB)

TOP

¦^´_ 15# jackyq

Å¥°_¨Ó¬O®¼²`ªº¡A¦³®É¶¡¦A¨Ó·j´M¬Ý¬Ý³o¨Ç¥Îªk¡A·PÁ±zªº¤À¨É»P±Ð¾Ç
«¢Åo~¤j®a¦n§r

TOP

¦^´_ 14# n7822123

·PÁ±zªº±Ð¾Ç¡A­ì¨Ó¥Î·Ó¤ù¤]¥i¥H°µ¦P¼Ëªº¥\¯à¡A¤S¾Ç¨ì¤@©Û
¤]·PÁ¤À¨Éºô§}¡A·|¦h¥h¬Ý¬Ýªº
«¢Åo~¤j®a¦n§r

TOP

§A­nªº¥\¯à·Q­n§Ë¨ì³Ì®e©ö¨Ï¥Î ( List ©ö·j , ¤ä«ù mousescroll )

´N¬O §â¥t¤@­Ó EXCEL ´O¤J¨ì ¥»Åé EXCEL

¥t¤@­Ó EXCEL ª½±µ®³¨Ó·í List

ª½±µ¤ä´© mousescroll ,  ºîÆ[¬d§ä,  ¤]¥i¿z¿ï¬d§ä

ListBox ³Ì¤j°ÝÃD:  No mousescroll , ªF¦è¤@¦h­n§ä¨ìªF¦è¦³ÂI¶O¤O

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD