²¾°Ê¦³ÃC¦âªº¦C¨ì¨ä¥Lsheet(¤p°ÝÃD½Ð±Ð)

jj369963

Dear¦U¦ì¤j¤j¡G

½Ð±Ð¤p°ÝÃD¡A¦pªþÀÉ¡A²¾°Ê¤w®æ¦¡¤Æ³]©wªº¦C¨ì¤£¦Psheet

¦A·Ð½Ð½×¾Âªº¦U¦ì¦h¦h«ü¾É¡AÁÂÁÂ

²¾°Ê¦³ÃC¦âªº¦C¨ì¨ä¥Lsheet_1030.rar (11.91 KB)

¤U¸ü¦¸¼Æ: 13

2013-10-30 22:14

jj369963

¦^´_ 14# Hsieh

Dear  Hsieh ¶Wª©:

          «D±`·PÁ±zªº¸É¥R»¡©ú¡AÁÙ¬O¤@¦p¬J©¹ªº¼ö¤ß¡Ait works.
           ¯u¬O¤Ó·PÁ¤F¡A¤]ÁÂÁÂGBKEE¼ö¤ßªº«ü¾É
            ·PÁÂ2¦ì

Hsieh

¦^´_ 13# jj369963
2003ª©¥»»P¨ä¥L·sª©ªº®æ¦¡¤Æ±ø¥ó¤½¦¡¡A
¨Ã¤£·|¦]¬°§@¥Î¤¤Àx¦s®æ¦ì¸m§ïÅܦÓÅܰʬ۹ï°Ñ·Óªº³¡¤À¤½¦¡¡A
©Ò¥H¡AÁ٧䤣¥X¥i­Ó§O§PÂ_±ø¥ó¦¨¥ß»P§_ªº¨ç¼Æ©Î¤èªk
°h¦Ó¨D¨ä¦¸¡A¸m´«®æ¦¡¤Æ±ø¥ó¤½¦¡ªº°Ñ·Ó¦C¦ì

1. Sub ex()
   
2. Dim A As Range, f As FormatCondition, Rng(1 To 2) As Range
   
3. With Sheet1
   
4.    For Each A In .Range(.[A2], .[C2].End(xlDown).Offset(, -2)).SpecialCells(xlCellTypeAllFormatConditions)
   
5.       For i = 1 To A.FormatConditions.Count
   
6.          If Evaluate(Replace(A.FormatConditions(i).Formula1, 1, A.Row)) > 0 Then
   
7.             If Rng(i) Is Nothing Then Set Rng(i) = Union(.[A1], A) Else Set Rng(i) = Union(Rng(i), A)
   
8.             Exit For
   
9.          End If
   
10.       Next
    
11.     Next
    
12. End With
    
13. sht = Array("µL±b±K", "µL§@µª")
    
14. For i = 1 To 2
    
15.    Rng(i).EntireRow.Copy Sheets(sht(i - 1)).[A1]
    
16.    Sheets(sht(i - 1)).UsedRange.FormatConditions.Delete '²M°£®æ¦¡¤Æ±ø¥ó
    
17. Next
    
18. End Sub

½Æ»s¥N½X

jj369963

¦^´_ 12# GBKEE


    Dear GBKEE ª©¤j¡G
  
   «D±`ÁÂÁ±zªº¦^ÂСA¦Ó¥B¦^ÂЪº¦n¸Ô²Ó¡A·PÁ¡C
¡y§A»¡ªº°õ¦æ»yªk«á¥u¦³¼ÐÃD¦C²¾¹L¥h¡A®æ¦¡¤Æªº¦C¨S¦³²¾¨ì¹ïÀ³ªºsheet,(¦Ê«ä¤£¸Ñ..)¡z
  ¦^ÂСG¦pªþÀÉ
¤£¹L§Ú¦b·Q·|¤£·|¬Oexcelª©¥»ªº®t²§(???)¡A¦]¬°ª©¤j¦³°õ¦æ¥X¨Ó¡A©Ò¥H°ÝÃDÀ³¸Ó¦b§Ú³oÃä¡C

  ÁÂÁª©¤j¥Î¤ß¥B¸Ô²Óªº¦^ÂÐ

¤U¸ü (505.51 KB)

2013-11-8 23:08

GBKEE

¦^´_ 10# jj369963
¦p¹Ï¥Ü¨Ì§A6#ªºÀÉ®×°õ¦æ7#ªºµ{¦¡,
§A»¡ªº°õ¦æ»yªk«á¥u¦³¼ÐÃD¦C²¾¹L¥h¡A®æ¦¡¤Æªº¦C¨S¦³²¾¨ì¹ïÀ³ªºsheet,(¦Ê«ä¤£¸Ñ..)
2003ª©¤¤ RGB(3, 255, 101)  ->  ¶Ç¦^¤@­Ó¼Æ­È  Criteria1=RGB(3, 255, 101) -> ³o¨ç¼Æ¶Ç¦^ªº¼Æ¦r

1. 'xlFilterCellColor³o°Ñ¼Æ¬°2010ª©ªº¤£¾A¥Î2003
   
2. ActiveSheet.Range("$A$1:$CQ$370").AutoFilter Field:=6, Criteria1:=RGB(3, 255, 101), Operator:=xlFilterCellColor

½Æ»s¥N½X

¤U¸ü (255.63 KB)

2013-11-7 15:15

luhpro

¦^´_ 4# jj369963
·|µo¥Í·¸¦ì¦³¥i¯à¬O Dim  i As Integer ªºÃö«Y,
¦]¬°¦C¸¹³Ì¤j¥i¹F¨ì65536 ,
©Ò¥H«Øij§ï¥Î Dim  i As Long ,
¸û¤£©öµo¥Í·¸¦ìªº±¡§Î.

¦^´_ 10# jj369963
¥H§A´£¨Ñªº½d¨ÒÀÉ®×,
®M¥Î 9# ªºµ{¦¡¨Ã¤£·|µo¥Í§A©Ò»¡ªº±¡§Î©O.
«Øij§A¹ï­ì©lÀɮפ¤ªº»Ý­n®æ¦¡¤Æ±ø¥óªº©Ò¦³Àx¦s®æ,
¦A½T»{¤@¦¸©Ò®M¥Îªº±ø¥ó¤º®e¬O§_³£¤@¼Ë.

¥u­n Mark ©Ò¦³»Ý®M¥Î¬Û¦P®æ¦¡±ø¥óªºÀx¦s®æ,
¦A«ö ®æ¦¡ -> ³]©w®æ¦¡¤Æ±ø¥ó ¬Ý¬Ý¬O§_³£¬O¬Û¦Pªº±ø¥ó¤½¦¡,
­Y¬O§A¬Ý¨ì "ªÅ¥Õ"(¨S¦³¤º®e) ªº±ø¥ó¤º®e,
¨º´N¬O³Q Mark ªºÀx¦s®æ¦³¤£¦Pªº±ø¥ó¦s¦b.
½Ð½T»{¬Ý¬Ý.

jj369963

¦^´_ 9# GBKEE

Dear GBKEEª©¤j:
±z¦n
1.´ú¸Õ«á¡A¥u·h²¾¼ÐÃD¦C¡A®æ¦¡¤Æªº¦C¨Ã¥¼·h²¾
ÁÂÁ¡A¦A·Ð½Ð«ü±Ð
¥t¥~³o¬O§Ú¥Î¿ý»s°µªº»yªk¨Ó®M¥Î¡A¤]¥X¿ù¡A¦ý®É¦³®É¦³§â³¡¤À¦C²¾¹L¥h¡A
¦A³Ò·Ð±z¬Ý¤@¤U¤F¡A·PÁÂ

1. Sub Macro1()
   
2. '
   
3.     Cells.Select
   
4.     Selection.AutoFilter
   
5.     ActiveSheet.Range("$A$1:$CQ$376").AutoFilter Field:=6, Criteria1:=RGB(250, _
   
6.         192, 144), Operator:=xlFilterCellColor
   
7.     Selection.Copy
   
8.     Sheets("µL§@µª").Select
   
9.     ActiveSheet.Paste
   
10.     Sheets("Sheet0").Select
    
11.     Application.CutCopyMode = False
    
12.     Rows("59:411").Select
    
13.     Selection.Delete Shift:=xlUp
    
14.     ActiveSheet.Range("$A$1:$CQ$370").AutoFilter Field:=6, Criteria1:=RGB(3, _
    
15.         255, 101), Operator:=xlFilterCellColor
    
16.     Selection.Copy
    
17.     Application.CutCopyMode = False
    
18.     Selection.Copy
    
19.     Rows("1:338").Select
    
20.     Application.CutCopyMode = False
    
21.     Selection.Copy
    
22.     Sheets("µL±b±K").Select
    
23.     ActiveSheet.Paste
    
24.     Sheets("µL±b±K").Select
    
25.     Rows("18:402").Select
    
26.     Application.CutCopyMode = False
    
27.     Rows("18:18").Select
    
28.     ActiveWindow.SmallScroll Down:=-3
    
29.     Rows("18:402").Select
    
30.     Selection.Delete Shift:=xlUp
    
31.     Selection.AutoFilter
    
32. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 8# jj369963
¦A¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Sh(1 To 2) As Worksheet, R As Integer, E As Range
   
4.     Set Sh(1) = Sheets("µL±b±K")
   
5.     Set Sh(2) = Sheets("µL§@µª")
   
6.      Sh(1).UsedRange.Clear
   
7.      Sh(2).UsedRange.Clear
   
8.     With Sheets("sheet0")
   
9.         Sh(1).Rows(1) = .Rows(1).Value
   
10.         Sh(2).Rows(1) = .Rows(1).Value
    
11.         .Activate
    
12.         For Each E In .UsedRange.Columns(1).Cells
    
13.             '2003ª© Àx¦s®æ³]©w®æ¦¡¤Æ¥i¦³3­Ó±ø¥ó¥i³]¤½¦¡
    
14.              E.Select          '»Ý¦b³]©w®æ¦¡¤ÆªºÀx¦s,¤è¥i­pºâ³]©w®æ¦¡¤Æ±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
15.             If E.FormatConditions.Count = 2 Then
    
16.                 If Application.Evaluate(E.FormatConditions(1).Formula1) Then       '­pºâ³]©w®æ¦¡¤Æ²Ä1­Ó±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
17.                     R = Sh(1).UsedRange.Rows.Count + 1                                          '­pºâ UsedRange.Rows ªºÁ`¼Æ+1
    
18.                     Sh(1).Rows(R) = E.EntireRow.Value
    
19.                 ElseIf Application.Evaluate(E.FormatConditions(2).Formula1) Then   '­pºâ³]©w®æ¦¡¤Æ²Ä2­Ó±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
20.                     R = Sh(2).UsedRange.Rows.Count + 1
    
21.                     Sh(2).Rows(R) = E.EntireRow.Value
    
22.                 End If
    
23.             End If
    
24.         Next
    
25.     End With
    
26. End Sub

½Æ»s¥N½X

jj369963

¦^´_ 7# GBKEE


    Dear GBKEEª©¤j¡G
±z¦n
ªþÀɦ³2­ÓÀÉ®×(¤u§@ªí¦WºÙ³£¬Û¦P)¦³¦P®É¶}±Ò,°õ¦æEXµ{¦¡¶Ü?
¦^ÂСG§Ú¨S¦³¦P®É¶}±Ò

¥t¥~°õ¦æ»yªk«á¥u¦³¼ÐÃD¦C²¾¹L¥h¡A®æ¦¡¤Æªº¦C¨S¦³²¾¨ì¹ïÀ³ªºsheet
³o¦¸¨S¦³¶]¥X¿ù»~µøµ¡
¤£ª¾°ÝÃD¦b­þ??
¦A·Ð½Ð¦³ªÅ¤§¾l¡A«ü±Ð»P¨ó§U

·PÁÂ

test_m_1.rar (62.79 KB)

¤U¸ü¦¸¼Æ: 9

2013-11-2 17:30

GBKEE

¦^´_ 6# jj369963
ªþÀɦ³2­ÓÀÉ®×(¤u§@ªí¦WºÙ³£¬Û¦P)¦³¦P®É¶}±Ò,°õ¦æEXµ{¦¡¶Ü?

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Sh(1 To 2) As Worksheet, i As Integer, R As Integer
   
4.     Set Sh(1) = ThisWorkbook.Sheets("µL±b±K")  'µ{¦¡½X©Ò¦b¬¡­¶Ã¯ªº¤u§@ªí
   
5.     Set Sh(2) = ThisWorkbook.Sheets("µL§@µª")
   
6.     '****************************************************************
   
7.     'Set Sh(1) = ActiveWorkbook.Sheets("µL±b±K")  '§@¥Î¤¤¬¡­¶Ã¯ªº¤u§@ªí
   
8.     '********************************************************************   
   
9.      Sh(1).UsedRange.Clear
   
10.      Sh(2).UsedRange.Clear
    
11.     With ThisWorkbook.Sheets("sheet0")            'µ{¦¡½X©Ò¦b¬¡­¶Ã¯ªº¤u§@ªí
    
12.     '****************************************************************
    
13.     'With ActiveWorkbook.Sheets("sheet0")         '§@¥Î¤¤¬¡­¶Ã¯ªº¤u§@ªí
    
14.     '****************************************************************
    
15.         Sh(1).Rows(1) = .Rows(1).Value
    
16.         Sh(2).Rows(1) = .Rows(1).Value
    
17.         .Activate
    
18.         i = 2
    
19.         Do While .Cells(i, "a").FormatConditions.Count = 2 And .Cells(i, "a") <> ""
    
20.             '2003ª© Àx¦s®æ³]©w®æ¦¡¤Æ¥i¦³3­Ó±ø¥ó¥i³]¤½¦¡
    
21.             .Cells(i, "a").Select          '»Ý¦b³]©w®æ¦¡¤ÆªºÀx¦s,¤è¥i­pºâ³]©w®æ¦¡¤Æ±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
22.             If Application.Evaluate(.Cells(i, "a").FormatConditions(1).Formula1) Then       '­pºâ³]©w®æ¦¡¤Æ²Ä1­Ó±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
23.                 R = Sh(1).UsedRange.Rows.Count + 1                                          '­pºâ UsedRange.Rows ªºÁ`¼Æ+1
    
24.                 Sh(1).Rows(R) = .Cells(i, "a").EntireRow.Value
    
25.             ElseIf Application.Evaluate(.Cells(i, "a").FormatConditions(2).Formula1) Then   '­pºâ³]©w®æ¦¡¤Æ²Ä2­Ó±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
26.                 R = Sh(2).UsedRange.Rows.Count + 1
    
27.                 Sh(2).Rows(R) = .Cells(i, "a").EntireRow.Value
    
28.             End If
    
29.             i = i + 1
    
30.         Loop
    
31.     End With
    
32. End Sub

½Æ»s¥N½X