²¾°Ê¦³Ã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

kimbal

¦^´_ 1# jj369963

¦Ñª©EXCEL¤£¯à¸gVBA¿ï¥X"±ø¥ó®æ¦¡"ªºÃC¦â.
¥i¥H¯uªí¥Î¥N½X¼g¥X

1. Sub test()
   
2. 
   
3.     Dim lngRowPwd As Long, lngRowNoAns As Long
   
4.     lngRowPwd = 1
   
5.     lngRowNoAns = 1
   
6.     For Each c In Range(Range("A1"), Range("A65536").End(xlUp))
   
7.         If Application.WorksheetFunction.CountA(Range(c, c.Offset(0, 1))) = 0 Then 'A:B
   
8.             Worksheets("µL±b±K").Rows(lngRowPwd).Value = Rows(c.Row).Value
   
9.             lngRowPwd = lngRowPwd + 1
   
10.         ElseIf Application.WorksheetFunction.CountA(Range(c.Offset(0, 7), c.Offset(0, 94))) <> 88 Then 'H:CQ
    
11.             Worksheets("µL§@µª").Rows(lngRowNoAns).Value = Rows(c.Row).Value
    
12.             lngRowNoAns = lngRowNoAns + 1
    
13.         End If
    
14. 
    
15.     Next
    
16. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 1# jj369963

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Sh(1 To 2) As Worksheet, i As Integer, R As Integer
   
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.         i = 2
    
13.         Do While .Cells(i, "a").FormatConditions.Count = 2
    
14.             '2003ª© Àx¦s®æ³]©w®æ¦¡¤Æ¥i¦³3­Ó±ø¥ó¥i³]¤½¦¡
    
15.             .Cells(i, "a").Select          '»Ý¦b³]©w®æ¦¡¤ÆªºÀx¦s,¤è¥i­pºâ³]©w®æ¦¡¤Æ±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
16.             If Application.Evaluate(.Cells(i, "a").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) = .Cells(i, "a").EntireRow.Value
    
19.             ElseIf Application.Evaluate(.Cells(i, "a").FormatConditions(2).Formula1) Then   '­pºâ³]©w®æ¦¡¤Æ²Ä2­Ó±ø¥ó¤½¦¡¬O§_¦¨¥ß
    
20.                 R = Sh(2).UsedRange.Rows.Count + 1
    
21.                 Sh(2).Rows(R) = .Cells(i, "a").EntireRow.Value
    
22.             End If
    
23.             i = i + 1
    
24.         Loop
    
25.     End With
    
26. End Sub

½Æ»s¥N½X

jj369963

¦^´_ 3# GBKEE


    Dear GBKEE ª©¥D¡G
   
              ÁÂÁ±zªº¦^À³¡A¦ý¬O¥X²{¤@ÂI°ÝÃD

            ¦pªG±z¦³ªÅ¡A¥i¥H¦A³Â·Ð±z¬Ý¤@¤U

         ·PÁÂ

¤U¸ü (23.46 KB)

2013-11-1 21:38

jj369963

¦^´_ 3# GBKEE

sorryÁÂÁ´£¿ô¡A¦pªþÀÉ

¤]ÁÂÁª©¤jªº¿n·¥¦^À³

test_m.rar (62.78 KB)

¤U¸ü¦¸¼Æ: 5

2013-11-1 21:55

jj369963

¦^´_ 2# kimbal


    Dear kimbal ª©¤j :
  
  Thanks your reply, but it didn't work.
1.¸ê®Æµ§¤Ö®É¡A¦³·h²¾¦ý¬O¥¼·h²¾§¹¥þ
2.¸ê®Æµ§¦h®É§¹¥þ¨S¤ÏÀ³

¦pªþÀÉ¡A½Ð°Ñ¾\

reply_1.rar (65.11 KB)

¤U¸ü¦¸¼Æ: 7

2013-11-1 22:11

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

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

¦^´_ 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

¦^´_ 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