stillfish00

¦^´_ 7# 198188
§AÀ³¸Óª`·N¤@¤UGª©ªºÁY¹Ï ,
¥L³o¼Ë°µ¤w¸g±N±ø¥ó°Ï©M¿z¿ïµ²ªG¤À¶}¤F,
§Aªºsheet2¤@¶}©l¬O§¹¥þªÅ¥Õ

stillfish00

¦^´_ 10# 198188
°Ñ¦Ò¬Ý¬Ý , §Q¥Î¶i¶¥¿z¿ï°µªº

¤U¸ü (82.89 KB)

2012-12-2 14:47

test.zip (19.54 KB)

¤U¸ü¦¸¼Æ: 4

2012-12-2 14:48

1. Sub myFilter()
   
2.     Dim rngSrc As Range, rngCopyField As Range, rngFilter As Range
   
3.     Dim nextRow As Long, endRow As Long
   
4.    
   
5.     Set rngSrc = Sheets("¸ê®Æ®w").[A1:G7]
   
6.     Set rngCopyField = Sheets("±ø¥ó°Ï").[B8:H8]
   
7.     Set rngFilter = Sheets("±ø¥ó°Ï").[B1].Resize(Sheets("±ø¥ó°Ï").[B1].CurrentRegion.Rows.Count, 8)
   
8.    
   
9.     nextRow = Sheets("¾ã²z°Ï").UsedRange.Rows.Count + 1
   
10.                
    
11.     rngSrc.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    
12.     rngFilter, CopyToRange:=Sheets("¾ã²z°Ï").Range("A" & nextRow)
    
13.         
    
14.     endRow = Sheets("¾ã²z°Ï").UsedRange.Rows.Count
    
15.    
    
16.     For i = 1 To rngCopyField.Count
    
17.         If rngCopyField(i) = "N" Then
    
18.             Sheets("¾ã²z°Ï").Range(nextRow & ":" & endRow).Columns(i).Clear
    
19.         End If
    
20.     Next
    
21.    
    
22.     Sheets("¾ã²z°Ï").Range("A" & nextRow).Resize(1, 7).Delete Shift:=xlUp   'delete header
    
23.    
    
24.     Set rngSrc = Nothing
    
25.     Set rngCopyField = Nothing
    
26.     Set rngFilter = Nothing
    
27. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 12# 198188
±ø¥ó³]©w³£¸ò¶i¶¥¿z¿ï·Ç«h¤@¼Ë
§A¥i¥H°Ñ¦Ò  http://office.microsoft.com/zh-tw/excel-help/HP010073942.aspx
¦p¿é¤J
=" "        ¿z¿ï¸ÓÄæ¦ì¬°³æ­ÓªÅ¥Õ¦r¤¸
="* *"    ¿z¿ï¸ÓÄæ¦ì¥]§tªÅ¥Õ¦r¤¸
=" *"      ¿z¿ï¸ÓÄæ¦ì¥HªÅ¥Õ¦r¤¸¶}ÀY

stillfish00

¦^´_ 14# 198188
§äªÅ¥ÕÀx¦s®æ , ¿é¤J
=   ©Î   ="="

stillfish00

¦^´_ 17# 198188

="=KEN"

test1.zip (723.23 KB)

¤U¸ü¦¸¼Æ: 8

2012-12-3 13:52

1.  §AÄæ¼Æ¦@¦³38Äæ resize¤]­n¸òµÛ§ï
2.  Result¤u§@ªí­n²MªÅ­Èªº®É­Ô , ­n¥Î¾ã¦C§R°£ ,
     §_«h§PÂ_Sheets("Result").UsedRange.Rows.Count¦C¼Æ®É
     ÁÙ·|¬O²MªÅ«eªº¦C¼Æ , ³oÂI§Ú¤]¤£ª¾¹D¦ó¸Ñ...

stillfish00

¦^´_ 19# 198188
¥i¥H¦Û¤v§ïªü , §Ú¬O¥H¬°§A­n¤@ª½·s¼W¤~³o¼Ë¼gªº..
    'nextRow = Sheets("Result").UsedRange.Rows.Count + 1
    nextRow = 2
    Sheets("Result").UsedRange.Offset(1).Clear
    ---²¤---
    'endRow = Sheets("Result").UsedRange.Rows.Count
    endRow = Sheets("Result").Range("A" & Sheets("Result").Rows.Count).End(xlUp)