AutoFilter ¡uŪ¥X¡vCriteriaªº§P§O¦¡
- ©«¤l
- 11
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 21
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office2011
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-5-5
- ³Ì«áµn¿ý
- 2015-6-27
|
AutoFilter ¡uŪ¥X¡vCriteriaªº§P§O¦¡
¤@¯ë±`¨£ªº³£¬O¼g¤Jcriteria«á¶i¦æ¿z¿ï
Ä´¦pn¿z¿ïCÄæ¬O"¥Ò"©Î"¤A"©Î"¤þ"ªºÀx¦s®æ
vba»yªk·|¬O Column("C:"C").Autofilter field:=1,Criteria1:=Array("¥Ò","¤A","¤þ")
²{¦b§Ú»Ýnªº¬O§Ú¦³«Ü¦hÄæn¶i¦æ¿z¿ï,
§Ú·Q§â¿z¿ïªº±ø¥ó¬ö¿ý¤U¨Ó,
¤§«á¦b¨ä¥L¦³¦P¼ËÄæ¦ìªºsheet¥Î³o¨Çcriteria¶i¦æ¿z¿ï
¤U±»yªk°£¤F¿z¿ï¡u®É¶¡¡v¥~¹B¦æ³£ok
«ä¸ô:
¥ý³Ð«Ø¤@°}¦CA,¥Î¨ÓÀx¦s¨CÄ檺¿z¿ï±ø¥ó
1.¥ý§PÂ_¸ÓÄæ¬O§_¦³¿z¿ï
2.¦pªG¬O,¦A§PÂ_Criteria1¬O§_¬O°}¦C
Y¬O°}¦C,«hªí¥Ü¿z¿ï¼Æ¶q>=3¥Bcriteria2µL©w¸q,
=>§âcriteria1Ū¤J°}¦CA
3.¦pªGcriteria1¤£¬O°}¦C,«h¿z¿ï¼Æ¶q¤£¬O1´N¬O2,
±µµÛ§PÂ_1©Î2ªº¤èªk¬O§Q¥Îoperator,¦pªGoperator¬O0,ªí¥Ü¿z¿ï¼Æ¶q¬O1
4.else´N¤@©w¬O¿z¿ï¼Æ¶q=2,¦¹®Écriteria1©Mcriteria2¦UÀx¦s¤@ÓÈ- Sub ¿z¿ï±ø¥ó()
- Range("A1:C10").Autofilter '¹ïA1¦ÜC10¶i¦æ¿z¿ï
- Dim ConditionArray(1 to 3) '³Ð¤@°}¦C,Àx¦s¨CÄ檺¿z¿ï±ø¥ó
- With Range("A1:C10").Autofilter
- For i=1 to .Filters.Count '³vÄæ§PÂ_
- If .Filters(i).On Then '§PÂ_¸ÓÄ榳µL¿z¿ï
- If isarray(.Filters(i).Criteria1) Then
- ConditionArr(i)=.Filters(i).Criteria1 '¸ÓÄæ¿z¿ï¼Æ¶q>=3,¥H°}¦C¤è¦¡¦s¤JConditionArr
- End if
- Elseif .Filters(i).Operator Then '¦pªG¬O0(¿z¿ï¼Æ¶q=1)«hªð¦^false
- ConditionArr(i)=.Filters(i).Criteria1
- Else
- Dim temp(1 to 2) '³Ð¤@¼È¦s°}¦C,Åý±ø¥ó1©M2¦s¦¨°}¦C
- temp(1)=.Filters(i).Criteria1
- temp(2)=.Filters(i).Criteria2
- ConditionArr(i)= temp ±N¼È¦s°}¦C¦s¤JConditionArr°}¦C
- End if
- Next
- End with
- End sub
½Æ»s¥N½X ¤§«á¦AŪ¥X§Y¥i- For i = 1 To 3
- If IsEmpty(CondiArr(i)) Then GoTo Jump
- If IsArray(CondiArr(i)) Then
- ReDim RedoArr(1 To UBound(CondiArr(i)))
- For j = 1 To UBound(CondiArr(i))
-
- RedoArr(j) = Mid(CondiArr(i)(j), 2, Len(CondiArr(i)(j)) - 1) '¥h°£µ¥¸¹
- Next j
- ActiveSheet.Range(Cells(1, 1), Cells(Last_row, LC2)).AutoFilter Field:=i, Criteria1:=RedoArr, Operator:=xlFilterValues
- Else
- ActiveSheet.Range(Cells(1, 1), Cells(Last_row, LC2)).AutoFilter Field:=i, Criteria1:=CondiArr(i), Operator:=xlFilterValues
- End If
- Jump:
- Next
½Æ»s¥N½X §Ú¹J¨ìªº§xÃø¬O,¦pªG¦³¬Y¤@Äæ¬O®É¶¡,
¥B®É¶¡Ä´¦p¿z5¤ë©M6¤ë,
³o¼Ë¦b¤@¶}©l§PÂ_isarrayªºcriteria1®É,·|¥X²{criteria1À³¥Îµ{¦¡¿ù»~
¥B³z¹Lcriteria2ºÊ¬Ý¦¡,·|µo²{criteria2¤]¬OÀ³¥Îµ{¦¡¿ù»~
¦ý³z¹L¿ý»s¥¨¶°(¿ý»s¿z¿ï®É¶¡)·|µo²{®É¶¡Äæ³£¦s¦bcriteria2
n¦p¦ó¤~¯à§â¿z¿ï®É¶¡ªº±ø¥óÀx¦s¤U¨Ó©O? |
|
MPEG
|
|
|
|
|
- ©«¤l
- 11
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 21
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office2011
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-5-5
- ³Ì«áµn¿ý
- 2015-6-27
|
¦^´_ 1# mpegwmvavi
QQ",³£¨S¤H¦^
§Ú¦Û¤v°µ¤FÓÀÉ®×(µL®É¶¡ªºÄæ¦ì¿z¿ï,©M¦³®É¶¡ªºÄæ¦ì¿z¿ï)
"¿z¿ï1"ªº¤u§@ªí¹B¦æ³£ok
¥i¥H¶¶§QŪ¥X¿z¿ï±ø¥ó,¨Ã¿é¥X¿z¿ï±ø¥ó¦b"¿é¥X¿z¿ï±ø¥ó1"³oÓsheet
"¿z¿ï2"ªº¤u§@ªí§â²Ä¥|Äæ´«¦¨®É¶¡
¦bnŪ¥X¿z¿ï±ø¥óªº®ÉÔ,·|¥X¿ù
¥X¿ùªº»y¥yºI¹Ï¦p¤U
http://iamjo.myweb.hinet.net/Error image.PNG
³z¹LºÊ¬Ý¦¡¥i¥H±oª¾µ{¦¡¶]¨ì®É¶¡³o¤@Äæ®É,criteria1©M2³£¨S¦³¦s¨ú¥ô¦óÈ
¦³¤Hª¾¹D«ç»òŪ¥X®É¶¡ªº¿z¿ï±ø¥ó¶Ü
§Æ±æ¦³°ª¤â«üÂI¤@¤U,ÁÂÁÂ
ÀÉ®×:
(¤@ª½¤£¯à¤W¶Çexcel,§Ú©ñ¦b§ÚªºªÅ¶¡¸Ì)
http://iamjo.myweb.hinet.net/Autofilter.xls |
|
MPEG
|
|
|
|
|
- ©«¤l
- 11
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 21
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office2011
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2011-5-5
- ³Ì«áµn¿ý
- 2015-6-27
|
¦^´_ 2# mpegwmvavi
§ä¨ì¥~°ê¬ÛÃöªº©«¤l,µo°ÝªÌ©M§Ú¹J¨ì¤@¼Ò¤@¼Ëªº°ÝÃDXD
http://answers.microsoft.com/en-us/office/forum/office_2007-customize/autofilter-criteria-with-xlfiltervalues-and-dates/90da7c5a-c813-4182-9849-c57ab72dac63
¿ý»s¿z¿ï®É¶¡Äæ·|µo²{,¿z¿ïªº±ø¥óÀx¦s¦bcriteria2
¦ý¬O«oµLªk§âcriteria2Ū¶i¥t¤@Ó°}¦CÀx¦s°_¨Ó
·|¥X²{À³¥Îµ{¦¡¿ù»~1004
©«¤l¨ì³Ì«á¤]¬OµL¸Ñ...
§Æ±æª©¥D¸s¯àÀ°À°§Ú°Ú~~:'(
ÁÂÁÂ |
|
MPEG
|
|
|
|
|