[µo°Ý] ½Ð±Ð¦p¦ó¦A±Nµ{§ÇÁY´î?

jonn0510

½Ð±Ð¦U¦ì¦Ñ®v:

¤U¦Cµ{§Ç¬O¥H F1~K1ªº¥ô·N¼Æ¦r¬°¤ñ¹ï­ì©l¸ê®Æ,N11~N15ªº¥ô·N¼Æ¦r­Y¦³»PF1~K1ªº¥ô·N¼Æ¦r¬Û¦PªÌ,«h¤À§O¦bN4~N8Åã¥Ü¼Æ¦r1 ,³Ì«á¦A¥[Á`©óN1³B.


Sub ZZ()
Set rng = [F1:K1]
For C = 14 To 24
  Range(Cells(3, C), Cells(8, C)).Clear
  Cells(1, C) = 0
  For r = 11 To 15
    If Application.CountIf(rng, Cells(r, C)) >= 1 Then
       Cells(r - 7, C) = 1
       Cells(1, C) = Cells(1, C) + 1
    End If
  Next r
Next C
End Sub

¦pªþ¥ó---------------¥H¤W¬O¤§«e½Ð±Ð¤j¤jªº¤èªk.
¦ý¬O¦]¬°¥u»Ý­n±NN11~N15ªº¥ô·N¼Æ¦r­Y¦³»PF1~K1ªº¥ô·N¼Æ¦r¬Û¦PªÌ¥[Á`,Åã¥Ü¦bN1³B§Y¥i.
¤£»Ý­n¤À§O¦bN4~N8Åã¥Ü¼Æ¦r1,¦]¬°·|¼W¥[ VBA »P¤º®eªº­t¾á.
©Ò¥H·Q­n¦bÁY´îµ{§Ç,¤£ª¾¹D¦æ¤£¦æ?ÁÂÁÂ

jonn0510

¦^´_ 2# Hsieh

Hsieh ¦Ñ®v:
ok!·PÁ±zªº±Ð¾É.
ª½±µÅã¥Ü³Ì«áµ²ªG,¯uªº¥i¥H¦b¬Ù¤U1/3ªº®É¶¡.ÁÂÁ±z

Hsieh

¦^´_ 1# jonn0510

1. Sub nn()
   
2. For Each a In Range([N11], [N11].End(xlToRight))
   
3.   mystr = "=SUMPRODUCT((COUNTIF($F$1:$K$1," & a.Resize(5, 1).Address & ")>0)*1)"
   
4.   a.Offset(-10, 0) = Evaluate(mystr)
   
5. Next
   
6. End Sub

½Æ»s¥N½X