[µo°Ý] (¤w¸Ñ¨M)¦p¦ó´M§äÃöÁä¦r¶ñ¤J¸ê®Æ

freeffly

§Ú·Q­n¦b"¸ê®Æ"¨º¤u§@ªí¿é¤J¸ê®ÆNÄæ¿é¤J¸ê®Æ
³W«h¦p"³W«h¨º¦U¤u§@ªí
­ì¥»§Ú¬O¥Î¿z¿ïªº¤è¦¡¥h§¹¦¨
¤£¹L§Úªºµ{¦¡½X«Ü¨S®Ä²v¸¨¸¨ªø
¤U­±³o¦U¬O¨ä¤¤¤@¤p³¡¥÷
¦pªG§Ú­n¥Î³W«h¥h¼g°j°é¦b¦Û°Ê¿z¿ï¤U¥i¦æ¶Ü?
±ø¥ó¤@¸ò±ø¥ó¤G°ò¥»¤W¬O"¥B"ªº®ÄªG
¦pªG­n¦³¥B¤S­n¦³©Î·|¤£·|¤ÓÃø?
¥Ø«e§Ú·Q»¡¦V¤U¼W¥[¶µ¥Ø
Åý±ø¥ó¤@¸ò±ø¥ó¤Gºû«ù¦b¥Bªº³¡¥÷

¤£ª¾¹D¦U¦ì°ª¤âª¾¹D§Úªº·N«ä¶Ü?

1.     On Error GoTo AA¶µ¥Ø:
   
2.     Selection.AutoFilter Field:=14, Criteria1:="="
   
3.     Selection.AutoFilter Field:=6, Criteria1:="=*¹ÎÅé¶Ë®`«OÀI*"
   
4.     Range("N5:N" & Range("A65536").End(xlUp).Row) = "¹ÎÅé¶Ë®`ÀI"
   
5. AA¶µ¥Ø:
   
6.     On Error GoTo AAA¶µ¥Ø:
   
7.     Selection.AutoFilter Field:=14, Criteria1:="="
   
8.     Selection.AutoFilter Field:=6, Criteria1:="=*¿³²M*"
   
9.     Range("N5:N" & Range("A65536").End(xlUp).Row) = "¿³²M"
   
10. AAA¶µ¥Ø:
    
11.     On Error GoTo AB¶µ¥Ø:
    
12.     Selection.AutoFilter Field:=14, Criteria1:="="
    
13.     Selection.AutoFilter Field:=6
    
14.     Selection.AutoFilter Field:=2, Criteria1:="=*¸³ºÊ¹S³Ò*"
    
15.     Range("N5:N" & Range("A65536").End(xlUp).Row) = "¦ô¦C"
    
16. AB¶µ¥Ø:

½Æ»s¥N½X

Book1.rar (66.99 KB)

¤U¸ü¦¸¼Æ: 9

2012-5-10 11:54

freeffly

¤U­±¬O§Ú·Qªº
¤£¹L¤£¯à°õ¦æ
½Ð°Ý¦³¤Hª¾¹D¸Ó¦p¦ó­×§ï¶Ü?

1. Sub Macro1()
   
2.    
   
3.     For i = 4 To Sheets("³W«h").Range("A65536").End(xlUp).Row
   
4.     n = Sheets("³W«h").Cells(i, 1)
   
5.     m = Sheets("³W«h").Cells(i, 2)
   
6.     w = Sheets("³W«h").Cells(i, 3)
   
7.     x = Sheets("³W«h").Cells(i, 4)
   
8.     If Sheets("³W«h").Cells(i, 3) = "" Then
   
9.     With Sheets("¸ê®Æ")
   
10.         .AutoFilter field:=1, criterial:="=" & n & ""
    
11.         .AutoFilter field:=6, Criteria1:="=*" & m & "*"
    
12.         .Range("N5:N" & Range("C65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible) = x
    
13.     End With
    
14.     Else
    
15.     With Sheets("¸ê®Æ")
    
16.         .AutoFilter field:=1, criterial:="=" & n & ""
    
17.         .AutoFilter field:=6, Criteria1:="=*" & m & "*", Operator:=xlAnd, Criteria2:="=*" & w & "*"
    
18.         .Range("N5:N" & Range("C65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible) = x
    
19.     End With
    
20.     End If
    
21.    
    
22.     Next
    
23.    
    
24. End Sub

½Æ»s¥N½X

freeffly

¦^´_ 3# register313


    ½Ð°Ý¤j¤j
   §ï³W«hªº¶¶§Çªº­ì¦]?
   §Ú­è­è¥uµo²{ "°h¥ðª÷¦ô­p®tÃB"³o¤@­Óªº¶¶§Ç­n©¹¤W
  ¨ä¥Lªº¦³¼vÅT¶Ü?
   UBound ¯u¬O¦U§®ªº¨ç¼Æ
  §Ú©ú¤Ñ¦A¸Õ¸Õ¬ÝÁÙ¦³¨S¦³¨ä¥L°ÝÃD

freeffly

¦^´_ 5# register313


    ³o¦U§Ú«á¨Óµo²{¤F
   ·í§Ú³W«hªº¸ê®Æ¶V¦h´N¶V­nª`·N³o¦U
   §Ú¦b¸Õ¸Õ¬Ý¦³¨S¦³¨ä¥L»Ý­nª`·Nªº
   
   ½Ð°Ý¦pªG§Ú¤W­±¥Îªº¨ººØ¼gªk¦³¿ìªk­×§ï¶Ü

freeffly

¦^´_ 5# register313


    ¤j¤j
   ­è°õ¦æ¦b§Úªº­ì©l¸ê®Æ¤~µo²{
   ¦³¤@­ÓÂI§A¨S§ì¨ì¬ì¥Ø½s¸¹
   ¦]¬°¤£¦Pªº¬ì¥Ø½s¸¹¸Ì­±ªº¤º®e¥i¯à¦³¨Ç¦r²´·|¬Û¦P
   
   ¸ê®Æ·Q­n¤@¬ì¥Ø½s¸¹ ¡B±ø¥ó¤@ ±ø¥ó¤Gªº±¡ªp¤U¥h§PÂ_
   ¦]¬°¦pªG¤£¦Ò¼{¬ì¥Ø½s¸¹¦³¨Ç¸ê®Æ·|¶]±¼
   ¦Ó¥B§Ú¦b½s³W«h®É¬O¨Ì·Ó¬ì¥Ø½s¸¹¤À§O¥h§PÂ_
   ¤£ª¾¹D¦³¨S¦³¤èªk§¹¦¨?
    §Ú¦³¸ÕµÛ§ï¦¨¤U­±ªº¤è¦¡¸Õ
   ¦ý¬Oµ²ªG¤£¹ï

1. Sub xx()
   
2. Application.ScreenUpdating = False
   
3. Ar = Sheets("³W«h").Range("A2:D" & Sheets("³W«h").Range("D65536").End(xlUp).Row)
   
4. With Sheets("¸ê®Æ")
   
5.   R = .[A4].End(xlDown).Row
   
6.   Range("N5:N" & R) = ""
   
7.   Set Rng = .Range("A4:P" & R)
   
8. End With
   
9. For I = 1 To UBound(Ar)
   
10.   Rng.AutoFilter Field:=14, Criteria1:="="
    
11.   Rng.AutoFilter Field:=1, Criteria1:="=*" & Ar(I, 1) & "*"
    
12.   Rng.AutoFilter Field:=6, Criteria1:="=*" & Ar(I, 2) & "*", Operator:=xlAnd, Criteria2:="=*" & Ar(I, 3) & "*"
    
13.   Range("N5:N" & R) = Ar(I, 4)
    
14.   Rng.AutoFilter
    
15. Next I
    
16. End Sub

½Æ»s¥N½X

freeffly

¦^´_ 8# register313
   ¥i¥H¤F
  ¤£¹L¬O§ï¤F³o¤@¥y¤§«áRange("N5:N" & R).SpecialCells(xlCellTypeVisible) = Ar(I, 4)
   ¦]¬°§Úªº­ì©l¸ê®Æ¬O±qA2¶}©l½s³W«h
  ÁÂÁ¤j¤j¨ó§U
  ¬Ù¤U§Ú«Ü¦h®É¶¡
  ¦Ó¥B¤]¹ïUBound³o¦U¨ç¼Æµy·L¦³¤F¸Ñ

1. Sub xx()
   
2. Application.ScreenUpdating = False
   
3. Ar = Sheets("³W«h").Range("A2:D" & Sheets("³W«h").Range("D65536").End(xlUp).Row)
   
4. With Sheets("¸ê®Æ")
   
5.   R = .[A4].End(xlDown).Row
   
6.   Range("N5:N" & R) = ""
   
7.   Set Rng = .Range("A4:P" & R)
   
8. End With
   
9. On Error Resume Next
   
10. For I = 1 To UBound(Ar)
    
11.   Rng.AutoFilter Field:=14, Criteria1:="="
    
12.   Rng.AutoFilter Field:=1, Criteria1:="=*" & Ar(I, 1) & "*"
    
13.   Rng.AutoFilter Field:=6, Criteria1:="=*" & Ar(I, 2) & "*", Operator:=xlAnd, Criteria2:="=*" & Ar(I, 3) & "*"
    
14.   Range("N5:N" & R).SpecialCells(xlCellTypeVisible) = Ar(I, 4)
    
15.   Rng.AutoFilter
    
16. Next I
    
17. End Sub

½Æ»s¥N½X