½Ð°Ýsheet Äæ¦ìÂà´«°ÝÃD

tonycho33

1.½Ð°Ý§Ú·Q±qa sheetÂà´«b sheet
2.¦bb sheet§¹¦¨®É¶¡¹ï·Ó¦³ok´N¯àÅýa sheetªº¹ïÀ³Äæ¦ì¤ÏÃC¦â

tonycho33

½Ð¨D¦U¦ì¨ó§U
ÁÂÁÂ

tonycho33

¦^´_ 32# Hsieh


    ½Ð°Ý¤@¤U
²{¦ba sheetÂàb sheet®æ¦¡®É¡A¤§«e¬O¤@¦¸¾ã­±Âà´«
¥i§_¤@¦¸¤T¦CÂà¡]¨C­Ó¤u³æ¤U¤è³£¦³«ö¶s¡^¡A ¤§«e¤Ï¬õ¤Î¤Ïºñªº¥\¯à¯àÄ~Äò¨Ï¥Î
¨Ò¦p¡G
¥Ø«e1286883¤u³æ¤U¤èªº«ö¶s¡A«ö¤@¤U¦AÂà¨ìb sheet
¥H¦¹Ãþ±À¡A«h·|©¹¤U¬ö¿ý¡A¦pªG¤w¦s¦bªº¤u³æ¡A§ó§ï¹LG¨ìSÄæ®É¡A¤ñ¹ï¤u³æ¸¹½X¡A«ö¤U«ö¶s«á¡A«hb sheet·|¸òµÛÅÜ°Ê
·sªº¤u³æ«h©¹¤UÄ~Äò¬ö¿ý
¨Ã¥B¥i¥H¸õµÛ¿é¤J¤£¤@©w­n«ö·Óa sheetªº¶¶§Ç

ÁÂÁÂ

Hsieh

¦^´_ 31# tonycho33

¸Õ¸Õ¬Ý

1. Private Sub Worksheet_Activate()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. With Sheet6
   
5. For Each A In .Range("I2", .[I65536].End(xlUp)).SpecialCells(xlCellTypeConstants)
   
6.    If A <> "" And A.Offset(, 1) = "" Then
   
7.    mystr = Join(Application.Transpose(Application.Transpose(A.Offset(, -8).Resize(, 5).Value)), "")
   
8.    d(mystr) = d(mystr) + 1
   
9.    ElseIf A.Offset(, 1) - A Then
   
10.    mystr = Join(Application.Transpose(Application.Transpose(A.Offset(, -8).Resize(, 5).Value)), "")
    
11.    d1(mystr) = d1(mystr) + 1
    
12.    End If
    
13. Next
    
14. End With
    
15. With Me
    
16. r = 2
    
17. Do Until .Cells(r, 1) = ""
    
18. Set A = .Cells(r, 1)
    
19. mystr = A & A.Offset(, 1)
    
20.    For Each c In .Range(A.Offset(, 6), A.Offset(, 6).End(xlToRight))
    
21.       temp = mystr & Join(Application.Transpose(c.Resize(3, 1)), "")
    
22.       If d.exists(temp) = True Then
    
23.       c.Resize(3, 1).Interior.ColorIndex = 4
    
24.       ElseIf d1.exists(temp) = True Then
    
25.       c.Resize(3, 1).Interior.ColorIndex = 6
    
26.       Else
    
27.       c.Resize(3, 1).Interior.ColorIndex = 0
    
28.       End If
    
29.    Next
    
30. r = r + 3
    
31. Loop
    
32. End With
    
33. End Sub

½Æ»s¥N½X

tonycho33

¦^´_ 29# Hsieh


    ½Ð°Ý¤@¤U ¤§«eªºµ{¦¡¤¤
     JÄ椧«e¿é¤J¡yOK¡z·|¤Ï¬õ
¥i¥H§ï¦¨JÄ榳¿é¤J®É¶¡ªº¸Ü¤Ï¬õ
                      ¥u¦³¿é¤JIÄæ¨S¦³¿é¤JJÄæ«h¤Ïºñ
¤u³æ¸¹½X¬Û¦P¤§¤U     J2©MI3ªº®É¶¡®t¶W¹L4¤p®É«h¤Ï¶À
¨ä¾l«hªÅ¥Õ
ÁÂÁÂ

tonycho33

¦^´_ 29# Hsieh


½Ð°Ý¤@¤U ¤§«eªºµ{¦¡¤¤
    JÄ椧«e¿é¤J¡yOK¡z·|¤Ï¬õ
¥i¥H§ï¦¨JÄ榳¿é¤J®É¶¡ªº¸Ü¤Ï¬õ
                     ¥u¦³¿é¤JIÄæ¨S¦³¿é¤JJÄæ«h¤Ïºñ
¤u³æ¸¹½X¬Û¦P¤§¤U     J2©MI3ªº®É¶¡®t¶W¹L4¤p®É«h¤Ï¶À
¨ä¾l«hªÅ¥Õ
ÁÂÁÂ

Hsieh

¦^´_ 28# tonycho33

1. Sub Âà¸m()
   
2. 
   
3. Dim A As Range, Ar(), Ay(), i%, j%, Ary(), s&
   
4. 
   
5. r = 2
   
6. 
   
7. With Sheets("a")
   
8. 
   
9. Do Until .Cells(r, 1) = ""
   
10. 
    
11. Set A = .Cells(r, 1)
    
12. 
    
13. Ar = A.Resize(, 4)
    
14. 
    
15. k = Application.CountA(.Range(A.Offset(, 6), A.Offset(, 6).End(xlToRight)))
    
16. If k = 0 Then GoTo 10
    
17. Ay = A.Offset(, 6).Resize(3, k).Value
    
18. 
    
19. For i = 1 To UBound(Ay, 2)
    
20. 
    
21. ReDim Preserve Ary(7, s)
    
22. 
    
23. Ary(0, s) = Ar(1, 1): Ary(1, s) = Ar(1, 2): Ary(6, s) = Ar(1, 4)
    
24. 
    
25.    For j = 1 To UBound(Ay, 1)
    
26. 
    
27.    Ary(j + 1, s) = Ay(j, i)
    
28. 
    
29.    Next
    
30. 
    
31.    s = s + 1
    
32. 
    
33. Next
    
34. 10
    
35. r = r + 3
    
36. 
    
37. Loop
    
38. 
    
39. End With
    
40. 
    
41. Sheets("b").UsedRange.Offset(1) = ""
    
42. 
    
43. Sheets("b").[A2].Resize(s, 7) = Application.Transpose(Ary)
    
44. 
    
45. End Sub

½Æ»s¥N½X

tonycho33

¦^´_ 19# Hsieh


    ½Ð°ÝA¡ãEÄæ·s¼W¦C¼Æ¡A¦ýG~SÄ楼·s¼W
³o¼Ë·|µ{¦¡·|¥X²{¿ù»~­n¦p¦ó­×§ï©O
ÁÂÁÂ

register313

¦^´_ 26# tonycho33

­Ó¤H¾Þ§@¨ÃµL '¤Á´«¦Üsheet a"·|ÅܺC¤§±¡§Î

1.´«¥x¹q¸£¸Õ¸Õ¬Ý
2.¸Ô²Ó»¡©ú§Aªº¾Þ§@¨BÆJ ·Q¿ìªkÅý§Úª¾¹Dª¬ªp
   ¤£µM§A»¡¦³°ÝÃD §Ú»¡¨S°ÝÃD ¥Ã»·µLªk¸Ñ§Ö

tonycho33

¦^´_ 25# register313


   §A¦n¡A¨Ï¥Î¹L«á¡AÁÙ¬O¤@¼Ëa sheet¤Á´««á·|ÅܺC
Ū¨ú®É«öesc «h¿ù»~°T®§°±¯d¦bNext
¦P¼Ëa2 sheet´N·|«Ü¥¿±`
½Ð¨D¨ó§U
ÁÂÁÂ