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

[µo°Ý] ½Ð°ÝÃö©ó¦Û°Ê¿z¿ï¨Ï¥Î°}¦C¤è¦¡­n¦p¦ó­×§ï

[µo°Ý] ½Ð°ÝÃö©ó¦Û°Ê¿z¿ï¨Ï¥Î°}¦C¤è¦¡­n¦p¦ó­×§ï

§Ú·Q­n¦A®w§O¨º¤@Ä椤´M§ä¥]§tB,C,D,Eµ¥¦r²´ªºµM«á¦A«~½è¨º¤@Äæ¿é¤J¹ïÀ³ªºB,C,D,E
¬O¤F´X¦¸³£¨S¿ìªk¦¨¥\
½Ð°Ý¤U­±­n¦p¦ó­×§ï?
  1. Sub «~½è_«á¨Óªº()
  2. n = Range("AG65536").End(xlUp).Offset(1).Row
  3. x = Range("A65536").End(xlUp).Row
  4. w = Array("*B*", "*C*", "*D*", "*E*")
  5. y = Array("B", "C", "D", "E")
  6. Selection.AutoFilter Field:=33, Criteria1:="="
  7. For i = LBound(w) To UBound(w)
  8. Selection.AutoFilter Field:=14, Criteria1:="=w(i)"
  9. Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = "" & y(i) & ""
  10. Next
  11. Selection.AutoFilter Field:=14
  12. Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = "A"
  13. Selection.AutoFilter Field:=33
  14. End Sub
½Æ»s¥N½X
  1. Sub «~½è_­ì¥ýªº()
  2. n = Range("AG65536").End(xlUp).Offset(1).Row
  3. x = Range("A65536").End(xlUp).Row
  4. w= Array("B", "C", "D", "E")
  5. Selection.AutoFilter Field:=33, Criteria1:="="
  6. For i = LBound(w) To UBound(w)
  7. Selection.AutoFilter Field:=14, Criteria1:="="* "& w(i) &""
  8. Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = "" & w(i) & ""
  9. Next
  10. Selection.AutoFilter Field:=14
  11. Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = "A"
  12. Selection.AutoFilter Field:=33
  13. End Sub
½Æ»s¥N½X
¦Û°Ê¿z¿ï°}¦C.rar (19.65 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

¸Õ¥X¨Ó¤F
¤£¹L§Ú·Q­nÅý¤U­±³o¨â¥y¥Î¤@¥y¼g´N¦n
­n¦p¦ó§ï?
w = Array("*B*", "*C*", "*D*", "*E*")
y = Array("B", "C", "D", "E")
  1. Sub «~½è()
  2. n = Range("AG65536").End(xlUp).Offset(1).Row
  3. x = Range("A65536").End(xlUp).Row
  4. w = Array("*B*", "*C*", "*D*", "*E*")
  5. y = Array("B", "C", "D", "E")
  6. Selection.AutoFilter Field:=33, Criteria1:="="
  7. For i = LBound(w) To UBound(w)
  8. Selection.AutoFilter Field:=14, Criteria1:="=" & w(i) & ""
  9. Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = "" & y(i) & ""
  10. Next
  11. Selection.AutoFilter Field:=14
  12. Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = "A"
  13. Selection.AutoFilter Field:=33
  14. End Sub
½Æ»s¥N½X
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2012-12-20 15:50 ½s¿è

¦^´_ 2# freeffly
  1. Sub «~½è()
  2.     n = Range("AG65536").End(xlUp).Offset(1).Row
  3.     x = Range("A65536").End(xlUp).Row
  4.     w = Array("B", "C", "D", "E")
  5.    
  6.     Selection.AutoFilter Field:=33, Criteria1:="="
  7.     For i = LBound(w) To UBound(w)
  8.     Selection.AutoFilter Field:=14, Criteria1:="=*" & w(i) & "*"
  9.     Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = w(i)
  10.     Next
  11.     Selection.AutoFilter Field:=14
  12.     Range(Cells(n, "AG"), Cells(x, "AG")).SpecialCells(xlCellTypeVisible) = "A"
  13.     Selection.AutoFilter Field:=33
  14. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# freeffly
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Range, W As Variant
  4.     For Each E In Range("N2", Range("N" & Rows.Count).End(xlUp))
  5.         For Each W In Array("*B*", "*C*", "*D*", "*E*")
  6.             If E Like W Then Cells(E.Row, "AG") = Replace(W, "*", "")
  7.         Next
  8.     Next
  9. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# stillfish00

    ¥i¥H¨Ï¥Î
    ¸ò§Ú²z¸Ñªº¨Ì¼Ë
   ¦n¹³¦³¼g¹L¤@¼Ëªºµ{¦¡½X(°£¤F¤W­±ªþªº)
   ¥i¬O¤w¸g»\¹L©Ò¥H¤]¤£ª¾¹D®t¦b­þ
   ÁÂÁÂ
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 4# GBKEE


    ÁÂÁª©¥D ­ì¨Ó¤]¦³³oºØ¤è¦¡
   ¾Ç²ß¤F
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 5# freeffly
¥u¬O§â§A#1
«~½è_­ì¥ýªº()
²Ä7¦æ§ï¥¿¦Ó¤w

TOP

        ÀR«ä¦Û¦b : ­×¦æ­nô½t­×¤ß¡AÂǨƽm¤ß¡AÀH³B¾i¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD