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

[µo°Ý] (¤w¸Ñ¨M)¥i¥H°w¹ï®æ¦¡¤Æ±ø¥óªºÃC¦â°µ§PÂ_¶Ü?

[µo°Ý] (¤w¸Ñ¨M)¥i¥H°w¹ï®æ¦¡¤Æ±ø¥óªºÃC¦â°µ§PÂ_¶Ü?

¥»©«³Ì«á¥Ñ freeffly ©ó 2012-2-22 17:03 ½s¿è

§Ú·Q¹ï®æ¦¡¤Æ±ø¥ó¤UªºÀx¦s®æ¥h°µ§PÂ_
¹ï©ó¨S¥Ñ®æ¦¡¤Æ¤WÃC¦âªº¦CÁôÂÃ
¥»¨Ó§Ú·Q¥Î¤U­±ªºµ{¦¡½X³B²z
µo²{®æ¦¡¤Æªº³¡¥÷¤£²Å¦X
½Ð¦U¦ì¤j¤jÀ°§Ú¬Ý¤@¤U­n«ç»ò§ï
§Úªº¼gªk¥i¯à¤]¤£¬O«Ü¥¿½T½ÐÀ°¦£­×¥¿




Sub ÁôÂææ¦C()
    Application.ScreenUpdating = False
    For i = 5 To Range("B65536").End(xlUp).Row
    Do While Cells(i, 2).Interior.ColorIndex = xlNone
    Cells(i, 2).EntireRow.Hidden = True
    i = i + 1
    Loop
    Next
End Sub
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

­n¥H¨ä®æ¦¡¤Æ±ø¥óªº¹Bºâ¦¡§PÂ_
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh
       ½Ð°Ý¦pªG§Úªº±ø¥ó¬O³o¼Ë §ÚÀ³¸Ó¦p¦ó¼g¤~¯à¹F¨ì§Ú­nªºµ²ªG?

     Columns("B:B").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=VLOOKUP($B1,$M$548:$M$556,1,0)=$B1"
    With Selection.FormatConditions(1).Font
        .Bold = False
        .Italic = False
        .ColorIndex = 4
    End With
    Selection.FormatConditions(1).Interior.Pattern = xlNone
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=FIND(""°ª¶¯"",$B1,1)"
    With Selection.FormatConditions(2).Font
        .Bold = True
        .Italic = False
    End With
    Selection.FormatConditions(2).Interior.Pattern = xlNone
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 3# freeffly
®æ¦¡¤Æ±ø¥ó¤£­n³o¼Ë¤U¤½¦¡¡A³o¼Ë±o¨ì¿ù»~­È®É¡AÁöµM®æ¦¡¤Æ±ø¥ó·|µø¬°¤£¦¨¥ß±ø¥ó
¦ý¹ê»Ú¤W¥¦¤£¬O¶Ç¦^ÅÞ¿è­È¡A·|³y¦¨³B²z¤Wªº§xÂZ
­×§ï¤@¤U¤½¦¡¡A¦p¹Ï
  1. Sub yy()
  2. Dim A As Range
  3. For Each A In Range("B:B").SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeAllFormatConditions)
  4.    A.Select
  5.    k = 0
  6.    For Each c In A.FormatConditions
  7.       s = c.Formula1
  8.    If Application.Evaluate(s) = True Then k = k + 1
  9.    Next
  10.    If k = 0 Then A.EntireRow.Hidden = True
  11. Next
  12. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-5-7 08:37 ½s¿è

®æ¦¡¤Æ±ø¥ó¦³   ¤½¦¡  ¤Î  Àx¦s®æªº­È ¨âºØ
  1. Sub Ex()
  2. Dim A As Range, k%, C As Object, F%(1)
  3. For Each A In Range("b:b").SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeAllFormatConditions)
  4.    k = 0
  5.    A.Select
  6.    For Each C In A.FormatConditions
  7.         If C.Type = 1 Then   '®æ¦¡¤Æ±ø¥ó 1Àx¦s®æªº­È
  8.             F(0) = C.Formula1
  9.             Select Case C.Operator
  10.                 Case 1  '>= AND <=
  11.                     F(1) = C.Formula2
  12.                     If A.Value >= F(0) And A.Value <= F(1) Then k = 1
  13.                 Case 2  '< or >
  14.                     F(1) = C.Formula2
  15.                     If A.Value < F(0) Or A.Value > F(1) Then k = 1
  16.                 Case 3  '=
  17.                     If A.Value = F(0) Then k = 1
  18.                 Case 4  '<>
  19.                     If A.Value <> F(0) Then k = 1
  20.                 Case 5  '>
  21.                     If A.Value > F(0) Then k = 1
  22.                 Case 6  '<
  23.                     If A.Value < F(0) Then k = 1
  24.                 Case 7  '>=
  25.                     If A.Value >= F(0) Then k = 1
  26.                 Case 8  '<=
  27.                     If A.Value <= F(0) Then k = 1
  28.             End Select
  29.         Else    '®æ¦¡¤Æ±ø¥ó ¤½¦¡
  30.             If Application.Evaluate(C.Formula1) = True Then k = 1
  31.         End If
  32.    Next
  33.    If k = 0 Then A.EntireRow.Hidden = True
  34. Next
  35. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# Hsieh


    ¤j¤j½T»{¤@¤U
  ®æ¦¡¤Æ±ø¥ó§Ú¸Ì­±ªº¤½¦¡³o¼Ë¬O¤£okªº¶Ü? ­n§ï¦¨¹³§A¨º¼Ë¶Ü?
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 5# GBKEE

   
¤j¤j§Aªº¤è¦¡¹ï§Ú¥Ø«eªº¥\¤O¦³ÂIÃø²z¸Ñ

¦pªG¦³®É¶¡¥i¥H»¡©ú¤@¤U¶Ü?
¦³¨Ç¨Ï¥Î¤è¬O§Ú¨S¬Ý¹L

k%  F%(1)  §@¥Î?  ()¸Ì­±¦³¤°»ò§®³B¶Ü?
«á­±case¸Ì­±§Ú¤w¸g¶Ã±¼¤F
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-5-8 20:54 ½s¿è

¦^´_ 7# freeffly
k%  F%(1)  §@¥Î?  ()¸Ì­±¦³¤°»ò§®³B¶Ü?
Dim  k% -> k As Integer                  '«Å§i ÅÜ¼Æ k ¬° Integer ¸ê®Æ«¬ºA «Å§i¦r¤¸¬O¦Ê¤À¤ñ²Å¸¹(%)¡C
Dim F%(1)-> F(0 To 1) As Integer    '«Å§i ÅܼƠ F ¬O(¤@ºû°}¦C,¬° 0 ¨ì 1­Ó¤¸¯À),    ¬° Integer ¸ê®Æ«¬ºA

For Each C In A.FormatConditions   A.Àx¦s®æªº®æ¦¡¤Æ±ø¥ó (³Ì¦h¥i¥H¦³¤T­Ó) ,C ¬°¨Ì§Ç©Ò³]ªº 1-3 ªº±ø¥ó
¹Ï¸Ñ






Select Case C.Operator -> ¨ÌC.Operator ªº¹Bºâ¤l ¨Ó°õ¦æµ{¦¡½X
¥i¹î¬Ý VBA ªº»¡©ú

TOP

¦^´_ 6# freeffly

§Aªº¤½¦¡¦b®æ¦¡¤Æ±ø¥ó¤¤¥i¥H¹F¨ì§Aªº¥Øªº¨S¿ù
¤£¹L¡A§Aªº¤½¦¡¦b±ø¥ó¤£¦¨¥ßªº±¡§Î¤U
¸Ó¤½¦¡¶Ç¦^­È¤£¤@©w¬OFLASE¡A¦³¥i¯à¬O¿ù»~­È
·íµM¿ù»~­È¥L¤£¬OTRUE©Ò¥HÀx¦s®æ¤£·|Åܦâ
¦ý¬O¡A³o­Ó¤½¦¡¦bVBA¤¤§Q¥ÎEvaluate¤èªk¨Ó¨D­È®É¡A¥Ñ©ó²£¥Í¿ù»~­ÈªºÃö«Y¡Aµ{¦¡´N·|¥X¿ù
©Ò¥H«Øij§A±N¤½¦¡­×§ï¦¨½T©w¶Ç¦^¬OÅÞ¿è­ÈTRUE©ÎFALSE
³o¼Ë´N¥i¬Ù¥h¥Îµ{¦¡©¿²¤¿ù»~ªºµ{§Ç
«Øij¤½¦¡§ï¬°
=COUNTIF($M$548:$M$556,$B1)>0
=ISNUMBER(FIND("°ª¶¯",$B1,1)
³o·|¤ñ§Ú¤W¤@­Ó¦^´_ªº¤½¦¡Â²³æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh


    ÁÂÁ¤j¤j¸Ñ»¡
      ¦³ºØÁŵM¶}®Ôªº·Pı
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD