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

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

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

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)

¦^´_ 1# jj369963

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

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

  13.     Next
  14. End Sub
½Æ»s¥N½X
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# GBKEE


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

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

         ·PÁÂ

TOP

¥»©«³Ì«á¥Ñ jj369963 ©ó 2013-11-1 22:03 ½s¿è

¦^´_ 3# GBKEE

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

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

test_m.rar (62.78 KB)

TOP

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

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ jj369963 ©ó 2013-11-3 18:10 ½s¿è

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

TOP

        ÀR«ä¦Û¦b : ÀR§¤±`®¦¤v¹L¡B¶¢½Í²ö½×¤H«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD