Board logo

¼ÐÃD: [µo°Ý] §Q¥Î¤U©Ô¦¡¿ï³æ¿ï¨ú¸ê®Æ¡A¨Ã¶i¦æ¶i¶¥¿z¿ï [¥´¦L¥»­¶]

§@ªÌ: Duck    ®É¶¡: 2014-7-24 18:32     ¼ÐÃD: §Q¥Î¤U©Ô¦¡¿ï³æ¿ï¨ú¸ê®Æ¡A¨Ã¶i¦æ¶i¶¥¿z¿ï

¦U¦ì¤j¤j­Ì:

·Q½Ð°Ý¤@¤U¡A§Ú·Q¥Î¤u§@ªí1ªº³o¨Ç¸ê®Æ¡A§Q¥Î"Âå®v¥N½X"³o­ÓÄæ¦ì°µ¥X¤U©Ô¦¡¿ï³æ¡A
¿ï¥X¤§«á§Æ±æ§e²{ª¬ºA¦p¤u§@ªí2¤@¼Ë¡A¨Ã¥BBÄæ¦ì»PEÄæ¦ì¶i¦æ¶i¶¥¿z¿ï(¤£­«½Æ)¡A¿ï¨ú¦bG»PIÄæ¦ì¤W¡A²ÄJÄæ¦ì¬O­pºâ¤½¦¡¡A³o¨Ç§@ªk¦p¦ó¥ÎVBA§e²{?

¥Ñ©ó¹ê»Ú¸ê®Æ¸û¬°Ãe¤j¡A¦p¥Î¤â°Ê¿z¿ï¤uµ{¯E¤j¡A¬G§Æ±æª½±µ¥ÎVBAªº»yªk¥h°õ¦æ¥¦¡A§Æ±æ¦U¦ì°ª¤â­ÌÀ°¦£!!! ·P¿E¤£ºÉ~~~
§@ªÌ: Hsieh    ®É¶¡: 2014-7-25 10:55

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-7-25 10:59 ½s¿è

¦^´_ 1# Duck

¤£­«½Æ©w¸q¬O§_B&D¤£­«½Æ?
[attach]18758[/attach]
¦pªG¬Oªº¸Ü
´¡¤J¦Û­qªí³æ¡A¥¬¸m¤@­Ó¤U©Ô²M³æ
ªí³æ¼Ò²Õ¤¤µ{¦¡½X¦p¤U
  1. Private Sub ComboBox1_Change()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. With ¤u§@ªí2
  4. Set Rng = ¤u§@ªí2.[A1]
  5. .[A:E].Clear
  6. With ¤u§@ªí1
  7.    With .Range("A1").CurrentRegion
  8.       .AutoFilter 4, ComboBox1
  9.       .SpecialCells(xlCellTypeVisible).Copy Rng
  10.       .AutoFilter
  11.    End With
  12. End With
  13. mystr = "=COUNTIF(C5,RC9)/(COUNTA(C7)-1)"
  14. For Each a In .Range(.[B2], .[B1].End(xlDown))
  15.     d(a & a.Offset(, 3)) = Array(a, "", a.Offset(, 3))
  16. Next
  17. .Range("G1").CurrentRegion.Offset(1).ClearContents
  18. .[G2].Resize(d.Count, 3) = Application.Transpose(Application.Transpose(d.items))
  19. .[J2].Resize(d.Count, 1).FormulaR1C1 = mystr
  20. .[H2] = d.Count
  21. End With
  22. Unload Me
  23. End Sub

  24. Private Sub UserForm_Initialize()
  25. Set d = CreateObject("Scripting.Dictionary")
  26. With ¤u§@ªí1
  27.    For Each a In .Range(.[D2], .[D2].End(xlDown))
  28.       d(a.Text) = ""
  29.    Next
  30. End With
  31. ComboBox1.List = d.keys
  32. End Sub
½Æ»s¥N½X
[attach]18759[/attach]
§@ªÌ: Duck    ®É¶¡: 2014-7-25 23:43

·PÁ°ª¤âªºÁ¸§U¡A¦ý¤£¦n·N«ä§Ú¨S§â°ÝÃD»¡²M·¡¡A§Ú¬O§Æ±æ²ÄBÄæ¦ì¿ï¨ú¤£­«´_¦ÜGÄæ¦ì¡A²ÄEÄæ¦ì¿ï¨ú¤£­«´_¦ÜJÄæ¦ì¡A¬O¥¦­Ì¦U¦Û¿ï¨ú¤£­«½Æ¡A©êºp~ »y·N¨S»¡¦n....

¥t¥~¡A§Ú·Q¦A½Ð±Ð¤@¤U¡A¤£ª¾¹D»yªk¬O§_¥i¥H°õ¦æ»¡¡A§Ú·Q¥ý¦bAÄæ¦ì¿ï¨ú¤£­«½Æ¦ÜLÄæ¦ì¡A«hMÄæ¦ì¥i¥H¥h¦Û°Ê­pºâ¥X¡ALÄæ¦ìªº¨C­Ó¤@¶µ¥Ø¥L­Ì¦bBÄæ¦ì¤£­«½Æ¥X²{ªº¦¸¼Æ¬O´X¦¸?!

¦p¤U¹Ï©Ò¥Ü¡A§Ú¥i¥H¦bLÄæ¦ì¸Ìªº"19168"³o­Ó¶µ¥Ø¤¤­pºâ¥X¥L¤À§O¦³2013061011226©M2013061011470³o¨âµ§¬ö¿ý¡A¬G¬O¦bMÄæ¦ì­pºâ¥X²{¤F2¦¸¡A¤£ª¾¬O§_¥i¥H°õ¦æ?



·Ð½Ð°ª¤â¯à¸Ñ±Ï¦b¤U...·P¿E¤£ºÉ~~
§@ªÌ: Duck    ®É¶¡: 2014-7-27 22:34

¦^´_ 2# Hsieh

©êºp~¥Ñ©ó§Ñ°O®×¦^ÂСA¬G¦b­«·sµo¤@½g~


·PÁ°ª¤âªºÁ¸§U¡A¦ý¤£¦n·N«ä§Ú¨S§â°ÝÃD»¡²M·¡¡A§Ú¬O§Æ±æ²ÄBÄæ¦ì¿ï¨ú¤£­«´_¦ÜGÄæ¦ì¡A²ÄEÄæ¦ì¿ï¨ú¤£­«´_¦ÜJÄæ¦ì¡A¬O¥¦­Ì¦U¦Û¿ï¨ú¤£­«½Æ¡A©êºp~ »y·N¨S»¡¦n....


¥t¥~¡A§Ú·Q¦A½Ð±Ð¤@¤U¡A¤£ª¾¹D»yªk¬O§_¥i¥H°õ¦æ»¡¡A§Ú·Q¥ý¦bAÄæ¦ì¿ï¨ú¤£­«½Æ¦ÜLÄæ¦ì¡A«hMÄæ¦ì¥i¥H¥h¦Û°Ê­pºâ¥X¡ALÄæ¦ìªº¨C­Ó¤@¶µ¥Ø¥L­Ì¦bBÄæ¦ì¤£­«½Æ¥X²{ªº¦¸¼Æ¬O´X¦¸?!

¦p¤U¹Ï©Ò¥Ü¡A§Ú¥i¥H¦bLÄæ¦ì¸Ìªº"19168"³o­Ó¶µ¥Ø¤¤­pºâ¥X¥L¤À§O¦³2013061011226©M2013061011470³o¨âµ§¬ö¿ý¡A¬G¬O¦bMÄæ¦ì­pºâ¥X²{¤F2¦¸¡A¤£ª¾¬O§_¥i¥H°õ¦æ?



·Ð½Ð°ª¤â¯à¸Ñ±Ï¦b¤U...·P¿E¤£ºÉ~~
§@ªÌ: Duck    ®É¶¡: 2014-7-28 19:29

¦^´_ 2# Hsieh


    Hsieh ±z¦n:

¤£ª¾§Úªº°ÝÃD¬O§_´y­z¦³Åý§A¤£²M·¡ªº¦a¤è¶Ü? ­þ­Ó³¡¤À¤£ÁA¸Ñ¡A§Ú¥i¥H¦A¥[¥H»¡©ú¡A§Æ±æ§A¯àÀ°À°§Ú³o­Óvbaªì¾ÇªÌ~ ¸U¤À·P¿E :)
§@ªÌ: Duck    ®É¶¡: 2014-7-30 20:08

¦^´_ 2# Hsieh


±z¦n¡A³o­Óµ{¦¡¶]¥X¨Ó¤u§@ªí2ªºGÄæ¦ì»PIÄæ¦ìÁÙ¬O"¦U¦Û"ÁÙ¬O·|¥X²{­«½Æªº±¡§Î¡A½Ð°Ý»yªk­n¦p¦ó­×§ï¡A¤~·|¥¦­ÌÄæ¦ì¬O¥X²{¦U¦Û¤£­«½Æªº±¡ªp??


¸÷¨D¤j®vÀ°¦£~~~~:dizzy:
§@ªÌ: Hsieh    ®É¶¡: 2014-7-31 10:36

¦^´_ 6# Duck
  1. Private Sub ComboBox1_Change()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. Set d1 = CreateObject("Scripting.Dictionary")
  4. Set d2 = CreateObject("Scripting.Dictionary")
  5. Set d3 = CreateObject("Scripting.Dictionary")
  6. d2("CHT_IDX") = "BÄ椣­«½Æ¼Æ" 'L:MªºÄæ¦ì¦WºÙ"
  7. d3("CHT_IDX") = "BÄ椣­«½Æ¼Æ"
  8. With ¤u§@ªí2
  9. Set Rng = ¤u§@ªí2.[A1]
  10. .[A:E].ClearContents '²M°£¤§«e¿z¿ïµ²ªG
  11. With ¤u§@ªí1
  12.    With .Range("A1").CurrentRegion
  13.       .AutoFilter 4, ComboBox1 '¨Ì¾Ú¤U©Ô¿ï³æ¿z¿ï¸ê®Æ
  14.       .SpecialCells(xlCellTypeVisible).Copy Rng '±N¿z¿ïµ²ªG½Æ»s¨ì²Ä¤G¤u§@ªí
  15.       .AutoFilter '¨ú®ø¿z¿ï
  16.    End With
  17. End With
  18. mystr = "=COUNTIF(C5,RC9)/(COUNTA(C7)-1)" 'JÄ椽¦¡
  19. For Each a In .Range(.[B2], .[B1].End(xlDown)) 'BÄæ¸ê®Æ°µ°j°é
  20.     d(a.Value) = "" 'Àx¦sDATESEQ¤£­«½Æ²M³æ
  21.     d1(a.Offset(, 3).Value) = "" 'Àx¦sPRICE_NAME¤£­«½Æ²M³æ
  22.     d3(a.Offset(, -1).Value) = _
  23.     IIf(InStr(d3(a.Offset(, -1).Value), a) = 0, d3(a.Offset(, -1).Value) & ";" & a, d3(a.Offset(, -1).Value)) '¥HAÄ欰¯Á¤Þ¡A­Y¥¼§tBÄæ¦r¦ê¡A«h¥H¤À¸¹;³sµ²BÄæ¦r¦ê
  24.     d2(a.Offset(, -1).Value) = UBound(Split(d3(a.Offset(, -1).Value), ";")) '¥H¤À¸¹¤Á³Î¦r¦ê¡A­pºâ¥X°}¦C¤¸¯À¼Æ¶q¡A§Y¬°¦PCHT_IDXªº¤£­«½ÆBÄæ¼Æ¶q
  25. Next
  26. .Range("G1").CurrentRegion.Offset(1).ClearContents
  27. .[L:M].ClearContents '²M°£L:MÄæ
  28. '¼g¤JG:MÄæ
  29. .[G2].Resize(d.Count, 1) = Application.Transpose(d.Keys)
  30. .[I2].Resize(d1.Count, 1) = Application.Transpose(d1.Keys)
  31. .[L1].Resize(d3.Count, 1) = Application.Transpose(d3.Keys)
  32. .[M1].Resize(d2.Count, 1) = Application.Transpose(d2.items)
  33. .[J2].Resize(d1.Count, 1).FormulaR1C1 = mystr
  34. .[H2] = d.Count
  35. End With
  36. Unload Me '¨ø¸üªí³æ
  37. End Sub
½Æ»s¥N½X

§@ªÌ: Duck    ®É¶¡: 2014-7-31 22:53

¦^´_ 7# Hsieh


    ·PÁ°ª¤âªºÁ¸§U! ¥i¥H¦¨¥\°õ¦æ¤F~~~ ÁÂÁÂ!!!
§@ªÌ: SWRovers    ®É¶¡: 2014-12-12 08:54

thanks ,¤S¾Ç²ß¨ì¤F
§@ªÌ: lostshin5208    ®É¶¡: 2015-3-17 10:32

ÁÂÁª©¥D¡A³o¥÷¸ê®Æ¤]Åý§Ú¸Ñ¨M§Úªº°ÝÃD¨Ã¥B¾Ç¨ì«Ü¦h¡I¡I¡I¡I
§@ªÌ: ChuckBucket    ®É¶¡: 2019-4-12 14:38

¦^´_ 2# Hsieh

«¢ÅoHsieh¤j¡A
§Ú¦b½m²ß¦¹©«ªº¹Lµ{µo²{¤@¨Ç°ÝÃD¡C
·í¤¤³o¬q¥N½X·Q­n½Ð±Ð±z :
  1. Private Sub UserForm_Initialize()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. With ¤u§@ªí1
  4.    For Each a In .Range(.[D2], .[D2].End(xlDown))
  5.       d(a.Text) = ""
  6.    Next
  7. End With
  8. ComboBox1.List = d.keys
  9. End Sub
½Æ»s¥N½X
§ÚÀ´³o¤@¬qªº¥Î·N¬O­n±N¡u­n¬d¸ßªº¤£­«½Æ¶µ¥Ø¡v±a¶i¤U©Ô¦¡¿ï³æªº¿ï¶µ¶µ¥Ø¡F
¦ý¬O¦b For Each a In .Range(.[D2], .[D2].End(xlDown))©MNext¤¤ªºd(a.Text) = ""
§Ú¤£¤Ó¤F¸Ñ¬°¦ó­n±N©Ò¦³¶µ¥Øªº¤å¦rÅܦ¨""¡A³o¬q¼¶¼gªº½t¥Ñ¬O¨Ó¦Û¦ó³B¡H
ÁٳҷбzÀ°¦£¸Ñ´b¡C
§@ªÌ: ­ã´£³¡ªL    ®É¶¡: 2019-4-13 10:52

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2019-4-13 10:54 ½s¿è

¦^´_ 11# ChuckBucket

¦r¨åÀÉ >> §Q¥Î"KEY"­È, ±a¤@­Ó"ITEM"
d(a.Text) = ""
a.Text ¬O KEY
"" ¬O ITEM, ³oITEM¥i¬°¥ô¦ó«¬ºA¸ê®Æ

¦]¥u¨ú¤£­«ÂÐ, ¥u¥Î±o¨ìKEY, ¤£°µ¨ä¥¦³B²z,
©Ò¥HITEM¥Î""ªÅ¦r²Å§Y¥i(¤]¥i¥Î¨ä¥¦¦r²Å©Î¼Æ¦r)
d(a.Text) = "A"
d(a.Text) = 1
³£¬O¥i¥H¹F¨ì¥Øªº, ¤£¹L¦b³o­Ó»Ý¨D¤W,"A"©Î1³£¨S¦³·N¸q
=============================
d(a.Text) = d(a.Text) + 1 ³o­Ó´N¥i¥H²Ö­p a.Text ªº¦¸¼Æ
¨ä¥¦¥Î³~¥i¦h¬Ý´X­Ó½d¨Ò©«§a!!!
§@ªÌ: ChuckBucket    ®É¶¡: 2019-4-14 21:37

¦^´_ 12# ­ã´£³¡ªL
Hi ­ã¤j¡A
·PÁ§Aªº¦^ÂСC
©Ò¥HHsieh¤j¦bPrivate subùØÀYFor eachùØÀYªº¼gªk¡Ad(a.text) = ""¨ä¹êµ¥¦P©ód.add  a , ""¶Ü¡H
§Ú¯à³o¼Ë²z¸Ñ¶Ü¡H
§@ªÌ: ­ã´£³¡ªL    ®É¶¡: 2019-4-15 09:48

¦^´_ 13# ChuckBucket


¬Oªº~~




Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)