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

[µo°Ý] ¼Æ¦r¦U¨ú1

¦^´_ 19# ziv976688


with [A2]: .formula="=COUNT(A4:A52)&""­Ó""" : .value=.value: end with

©Î A2=application.COUNT(xS.[A4:A52]) & "­Ó"

TOP

15¼Ó¼g±o«Üºë²
¾Ç²ß­«¼g¦p¤U
Private Sub CommandButton1_Click()
    Set xD = CreateObject("Scripting.Dictionary")
    Tm = Timer
    Application.ScreenUpdating = False    '¦b­I´º¤U°õ¦æ
    For Each xS In Sheets(Array("·Ç2¶i3", "·Ç3¶i4", "·Ç4¶i5", "·Ç5¶i6", "·Ç6¶i7", "·Ç7¶i8")) '¨úªí®æ
        For Each xR In xS.Range("D2:J" & xS.[B65536].End(xlUp).Row) '¨úÀx¦s®æ
            For Each sp In Split(xR, ",") '¤À¶}¼Æ¦r
                If Val(sp) > 0 Then xD(Val(sp)) = s '¦r¨å²Õ¦X
            Next
        Next
        xS.[A4].Resize(99).ClearContents '²M°£Àx¦s®æ¤º®e
        xS.[a2] = xD.Count & "­Ó": xS.[A3] = "¸¹½X"
        If xD.Count = 0 Then Exit For
        With xS.[A4].Resize(xD.Count)
            .Value = Application.Transpose(xD.keys): xD.RemoveAll
            .Sort key1:=.Item(1), Order1:=xlAscending, Header:=xlNo
        End With
    Next
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

´ú¸ÕOK¤F!
·PÁ±zªº­@¤ß«ü¾É

TOP

¦^´_ 21# ­ã´£³¡ªL
²Ä¤G¬q¬O§Ú·Q¾Ç²ßªº~
·PÁª©¥Dªº­@¤ß«ü¾É

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-7-20 16:29 ½s¿è

¦^´_ 22# ML089
¤£¦n·N«ä¡A¦^´_20¼Ó®É¡A§Ñ¤FÂI¦^´_Áä¡C

¶Qµ{¦¡½X³£¦³µù¸Ñ¡A¹ï§Ú¦b¾Ç²ß¤WÀ°§U¬Æ¤j~·P®¦
§¹¥þOK¤F!
±z¾Ç²ß±o¯u§Ö¡A§Ú´N®t»·¤F¡C
ÁÂÁ±zªº­@¤ß«ü¾É©MÀ°¦£

TOP

¦^´_ 12# samwang
¦^´_ 20# ML089
¦^´_ 21# ­ã´£³¡ªL
http://forum.twbts.com/thread-23266-1-1.html
Àµ½Ð¦U¦ì¤j¤j«ü¾É©MÀ°¦£ !  ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-7-22 19:52 ½s¿è

¦^´_ 22# ML089
¼Æ¦r¦U¨ú1_ML089.rar (84.3 KB)
¤£¦n·N«ä¡A±N³o´X¤Ñªº°ÝÃD¸Ñµª¾ã²z«á¡A
·Q¦A¦V±z½Ð±Ð´X­Ó¤p°ÝÃD :
µ{¦¡½X¦bModule3
1_¦C6ªºsp¬O¤°»ò²[¸q?  special?
»Ý¤£»Ý­n³]¥ßÅܼÆ?
2_§Ú²{¦b³]¥ßªºÅܼƹ藍¹ï?°÷¤£°÷¥þ?
ÁÂÁ±z
PS : ¥Ø«eÀÉ®×°õ¦æ¥¿±`~¥iRun¥X¥¿½Tµª®×¡C

TOP

¦^´_ 21# ­ã´£³¡ªL
Sub ¾l¼Æ¦U¨ú1()
Dim Brr(1 To 99, 0), xS As Worksheet, A, B
For Each xS In Sheets(Array("·Ç2¶i3", "·Ç3¶i4", "·Ç4¶i5", "·Ç5¶i6", "·Ç6¶i7", "·Ç7¶i8"))
    For Each A In xS.Range("d2:j" & xS.[b65536].End(xlUp).Row + 1).Value
        For Each B In Split(A, ",")
           Brr(B, 0) = B
        Next B
    Next
    With xS.[a4].Resize(99)
         .Value = Brr:  Erase Brr
         .Sort Key1:=.Item(1), Order1:=xlAscending, Header:=xlNo
    End With
'   With xS.[a2] = Application.Count(xS.[A4:A52]) & "­Ó": End With
Next
End Sub
½Ð°Ý :
¦C13¬O§Ú¼g¿ùµ{¦¡½X¡AÁÙ¬O§Ú©ñ¿ù¦ì¦C?
½Ð±z½ç¥¿¡C
ÁÂÁ±z

TOP

¦^´_ 27# ziv976688

1_¦C6ªºsp¬O¤°»ò²[¸q?  special?
»Ý¤£»Ý­n³]¥ßÅܼÆ?
2_§Ú²{¦b³]¥ßªºÅܼƹ藍¹ï?°÷¤£°÷¥þ?

SP¬O¤@­ÓÅܼơA¦WºÙ¬°ÀH·N©w¡A·íªì¬O¥H Split ²¼g
¦nªºµ{¦¡À³¸Ó¬O­n©w¸qÅܼƤñ¸ûÄYÂÔ¡A¦]¬°VBA¤£±j­¢«Å§iÅܼơA¥[¤W§Ú¬O·~¾l¼g¤pµ{¦¡´N¨S²ßºD©w¸qÅܼơC

³o¤pµ{¦¡¦³4­ÓÅܼƥi¥H©w¸q¦p¤U   
Dim xD As Object, xS As Worksheet, xR As Range, SP
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 29# ML089
ª©¥D :±z¦n!
ÁA¸Ñ¤F!
·PÁ±zªº­@¤ß«ü¾É©MÀ°¦£

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD