[µo°Ý] [¨D§U]Äæ¦ì¤ñ¸û¨Ã­p¼Æªº¤èªk

a703130

½Ð°Ý¦U¦ì¤j¦Ñ¡A¦³ª¦¹L¬ÛÃö¤å³¹¡A³£µLªkÀò±o§Ú·Q­nªº¸Ñµª(¤p¾Ç¥ÍÁÙµLªk¤U¸üÀÉ®×)¡A©Ò¥H¦b³oÃ䦳´X­Ó°ÝÃD·Q½Ð±Ð¡G
1.¦p¦b¦¹sheet1¤W¡A¦p¤U¦C©Ò¥Ü¡A§Ú­n¦p¦ó¥Îµ{¦¡ª¾¹D(BÄæ¦ì²Ä2¦C)¶}©l¦³¸ê®Æ¡A¥B¦@¦³9¦C¸ê®Æ??
2.¦pªG·Q­n±oª¾BÄæ¦ì¤W¨Cµ§¸ê®Æ­«½Æªº¦@¦³´Xµ§¨ÃÅã¥Ü¦bCÄæ¦ì¤W¡A¦p¤U¦C©Ò¥Ü¡A¸Ó¦p¦ó°µ??

½Ð¦U¦ì¤j¦Ñ¤£§[§iª¾¡AÁÂÁÂ

         A         B           C
   2          0x0001       4
   3          0x0001
   4          0x0001
   5          0x0001
   6          0x0002       3
   7          0x0002
   8          0x0002
   9          0x0003       1

Hsieh

¦^´_ 1# a703130

1. Sub nn()
   
2. Dim B As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set B = Columns("B").Find("*")
   
5. If Not B Is Nothing Then r = B.Row Else Exit Sub
   
6. Do Until Cells(r, 2) = ""
   
7. If d.exists(Cells(r, 2).Value) = False Then
   
8. Cells(r, 3) = Application.CountIf(Columns("B"), Cells(r, 2))
   
9. d(Cells(r, 2).Value) = Cells(r, 2).Value
   
10. Else
    
11. Cells(r, 3) = ""
    
12. End If
    
13. r = r + 1
    
14. Loop

½Æ»s¥N½X

a703130

¦^´_ 2# Hsieh


ÁÂÁ¤j¦ÑÀ°¦£¡A¤£¹L¤´¦³¨Ç°ÝÃD¡A§Æ±æ¯àÀ°¦£¦^µª

Set B = Columns("B").Find("*") -- ¬O³]©wBÄæ¤@­Ó½d³ò¨S¦³«ü©wªø«×ªº·N«ä¶Ü??

If Not B Is Nothing Then R = B.Row Else Exit Sub  -- ·N«ä¬OBÄæ¦pªG¦³ªF¦è«hR=BÄ檺¦C¼Æ¶Ü??

If d.Exists(Cells(R, 2).Value) = False -- ³o¬qªº·N«ä¬O«ü??

ÁÂÁ¤j¦Ñ¤£§[½ç±Ð~!

Hsieh

¦^´_ 3# a703130

Set B = Columns("B").Find("*") -- ¬O³]©wBÄæ¤@­Ó½d³ò¨S¦³«ü©wªø«×ªº·N«ä¶Ü??
BÄæ§ä¨ì¦³¸ê®Æªº²Ä¤@­Ó¦ì¸m«ü©wµ¹ÅܼÆB

If Not B Is Nothing Then R = B.Row Else Exit Sub  -- ·N«ä¬OBÄæ¦pªG¦³ªF¦è«hR=BÄ檺¦C¼Æ¶Ü??
°²¦pBÄ榳¸ê®Æ¦s¦b´N±NÅܼÆR«ü©w¬°BÄæ§ä¨ì¦³¸ê®Æªº²Ä¤@­Ó¦ì¸mªº¦C¸¹§_«hµ²§ôµ{¦¡

If d.Exists(Cells(R, 2).Value) = False -- ³o¬qªº·N«ä¬O«ü??
¦pªG¦b¦r¨åª«¥ód¸Ì­±¤ñ¹ï¤£¨ìBÄ檺­È

hugh0620

¤£¤@©w­n¥Îµ{¦¡­ò~
¨ä¹ê¥Î¨ç¼Æ¤]¬O¥i¥H¸Ñ¨M­ò~ ´£¨Ñ¤@­Ó¨ç¼Æªº³B²z¤è¦¡~ ¶È¨Ñ°Ñ¦Ò
=IF(B1=B2,"",COUNTIF($B$2 : $B$65536,B2))

¥u¬O·í¸ê®Æ¦h©ó§Aªº¨ç¼Æ¦C®É~ ­n¦Û¤v¦A±N¨ç¼Æ©¹¤U©Ô~

a703130

¤£¦n·N«ä¤@ª½µo°Ý¡AVBAªº¨ç¼ÆÁÙ¯u³Õ¤jºë²`...

Set B = Columns("B").Find("*")
BÄæ§ä¨ì¦³¸ê®Æªº²Ä¤@­Ó¦ì¸m«ü©wµ¹ÅܼÆB


½Ð°Ý ("*") ¬O¥Nªí¦³¸ê®Æªº²Ä¤@­Ó¦ì¸m¶Ü??
¥¼¨Ó¦pªG­n·j´M¯S©wªº¦r¦ê©Î¬O¤å¦rªºªº¤@­Ó¦ì¸m¤]¥i¥H¦pªk¬¶»s¶Ü??

Hsieh

¦^´_ 6# a703130


    FIND¥Îªk½Ð¸Ô¨£VBA»¡©ú

FAlonso

1. Sub my()
   
2. Dim mydic As Object, mycell As Range, i As Integer, j As Integer, ar()
   
3. Set mydic = CreateObject("scripting.dictionary")
   
4. For Each mycell In Range([b1], [b65536].End(xlUp))
   
5. If mydic.exists(mycell.Value) Then
   
6. mydic(mycell.Value) = mydic(mycell.Value) + 1
   
7. Else
   
8. mydic(mycell.Value) = 1
   
9. j = mycell.Row
   
10. i = i + 1
    
11. ReDim Preserve ar(i)
    
12. ar(i) = j
    
13. End If
    
14. Next
    
15. For i = 1 To UBound(ar)
    
16. Cells(ar(i), 3).Value = mydic(Cells(ar(i), 2).Value)
    
17. Next
    
18. Set mydic = Nothing
    
19. End Sub

½Æ»s¥N½X

µ{¦¡¼g¨ì¤@¹ÎÁV,«j±j§¹¦¨

a703130

¦^´_ 2# Hsieh


If d.Exists(Cells(R, 2).Value) = False Then

Cells(R, 3) = Application.CountIf(Columns("B"), Cells(R, 2))

d(Cells(R, 2).Value) = Cells(R, 2).Value

sorry ¤j¨Ð~³o¬qµ{¦¡À³¸Ó¬O§PÂ_¬O§_¦³­«½ÆªºÄæ¦ì¨Ã¥B­p¼Æ

¦U¦æµ{¦¡ªº¥D­n·N«ä¬O§_¯à¤j·§»¡©ú¤@¤U??

ºô¸ô¬O¥i¥H§ä¡A¤£¹L¤j¨ÐÄ@·NÀ°¦£¡A¤W¤â·|§ó§Ö¤@ÂI^^"

¤@ª½½Ð±Ð¤£¦n·N«ä~

Hsieh

¦^´_ 9# a703130


    If d.Exists(Cells(R, 2).Value) = False Then
¤§©Ò¥H¨Ï¥Î¦r¨å¨ÓÀˬd¬O§_¸Ó¯Á¤Þ¤w¸g¦s¦b¡A¬O¦]¬°§Aªº­p¼Æ¦ì¸m¬O¦b¸Ó©Ò¤Þªº²Ä¤@­Ó¦ì¸mÅã¥Ü
±q²Ä2­Ó¦ì¸m¥H«á´N¤£¦AÅã¥Üµ²ªG¡A¬G¥²¶·§PÂ_¯Á¤Þ¬O§_¦s¦b
Cells(R, 3) = Application.CountIf(Columns("B"), Cells(R, 2))
±N­p¼Æªº­È¶ñ¤JCÄæ
d(Cells(R, 2).Value) = Cells(R, 2).Value
±N¯Á¤Þ­È¦s¨ì¦r¨åª«¥ó¤¤¡Aª`·N¤@ÂI¡A­Y¯Á¤Þ­È¹ïÀ³ªº¤º®e¬Od(Cells(R, 2).Value) =""®É¡AExists¤èªk·|±o¨ìFALSE
¤]´N¬O»{¬°¸Ó©Ò¤Þ­È¤£¦s¦b©ó¦r¨åª«¥ó
¦pªG³o¼ËµLªk²z¸Ñ¡A¸Õ¸Õ¤U­±µ{¦¡½X
¥Ñ¤U©¹¤W¨Ó°µ°j°é

1. Sub nn()
   
2. r = [B65536].End(xlUp).Row 'BÄ榳¸ê®Æªº³Ì¤j¦C¦ì
   
3. Do Until Cells(r, 2) = "" 'ª½¨ìBÄæ¨S¦³¤º®eµ²§ô°j°é
   
4.    Set a = Cells(r, 2) '³]©wÅܼÆa¬°Äæ¦ìªºÀx¦s®æ
   
5.    Set b = Range([B1], a.Offset(-1, 0)).Find(a) '¦bB1¨ìªº¤W¤@­ÓÀx¦s®æ¤¤´M§äa
   
6.    '¦pªG§ä¤£¨ì´N±N­p¼Æ¼g¤JCÄæ¡A§_«hCÄæ¦ì¸m´N²MªÅ
   
7.    If b Is Nothing Then a.Offset(, 1) = Application.CountIf(Columns("B"), a) Else a.Offset(, 1) = ""
   
8.    r = r - 1 '¦C¦ì´î1
   
9. Loop
   
10. End Sub

½Æ»s¥N½X