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

½Ð¦Ñ®v«ü¾É¦p¦ó±Nµ{§ÇÁY´î?

½Ð¦Ñ®v«ü¾É¦p¦ó±Nµ{§ÇÁY´î?

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

Dim C%, r%, rng As Range
Set rng = [F1:K1]
For C = 14 To 14
For r = 11 To 11
If Application.CountIf(rng, Cells(r, C)) >= 1 Then
Cells(4, C) = 1: Exit For
End If
Next r
Next C

Set rng = [F1:K1]
For C = 14 To 14
For r = 12 To 12
If Application.CountIf(rng, Cells(r, C)) >= 1 Then
Cells(5, C) = 1: Exit For
End If
Next r
Next C

Set rng = [F1:K1]
For C = 14 To 14
For r = 13 To 13
If Application.CountIf(rng, Cells(r, C)) >= 1 Then
Cells(6, C) = 1: Exit For
End If
Next r
Next C

Set rng = [F1:K1]
For C = 14 To 14
For r = 14 To 14
If Application.CountIf(rng, Cells(r, C)) >= 1 Then
Cells(7, C) = 1: Exit For
End If
Next r
Next C

Set rng = [F1:K1]
For C = 14 To 14
For r = 15 To 15
If Application.CountIf(rng, Cells(r, C)) >= 1 Then
Cells(8, C) = 1: Exit For
End If
Next r
Next C

Range("N3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[7]C)"
Range("N4").Select         
----------------------------------------------------------------
°ÝÃD¤@
§Æ±æ§ï¦¨¤U¦C³o¼Ë¯àÁYµuµ{§Ç,«oµo²{¤£¯à²Ö¥[.¤£ª¾¹D¸Ó¦p¦ó³B²z?
Dim C%, r%, rng As Range
Set rng = [F1:K1]
For C = 14 To 14
For r = 11 To 15 '³o¸Ì§ó§ï
If Application.CountIf(rng, Cells(r, C)) >= 1 Then
Cells(4, C) = 1: Exit For
End If
Next r
Next C

°ÝÃD¤G
¦pªG¹³ªþ¥óN11~N16.O11~O16.P11~P16 µ¥,­Y­n©µªø¦ÜBL11~BL16.¤£ª¾¹D¯à§_¦AÁYµuµ{§Ç©O?

¦]¬°¤p§ÌªºÅv­­¤£°÷¤U¸ü,¯à§_±Nµ{§Ç´y­z¦bºô­¶¤W.ÁÂÁÂ

XYZ.rar (9.51 KB)

·PÁ¦U¦ì¦Ñ®vªá¤ß«ä±Ð¾É

¦^´_ 1# jonn0510
ªì¾ÇªÌVBA
  1. Sub ZZ()
  2. Set rng = [F1:K1]
  3. For C = 14 To 64
  4.   Range(Cells(3, C), Cells(8, C)).Clear
  5.   Cells(3, C) = 0
  6.   For r = 11 To 15
  7.     If Application.CountIf(rng, Cells(r, C)) >= 1 Then
  8.        Cells(r - 7, C) = 1
  9.        Cells(3, C) = Cells(3, C) + 1
  10.     End If
  11.   Next r
  12. Next C
  13. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# jonn0510
  1. Sub Ex()
  2.     Dim Rng(1 To 2) As Range, E As Range
  3.     Set Rng(1) = [F1:K1]
  4.     Set Rng(2) = Range("N11").CurrentRegion
  5.     Rng(2)(1).Offset(-8).Resize(Rng(2).Rows.Count + 1, Rng(2).Columns.Count) = ""
  6.     For Each E In Rng(2)
  7.         If Application.CountIf(Rng(1), E) >= 1 Then
  8.             E.Offset(-7) = 1
  9.             Cells(Rng(2)(1).Row - 8, E.Column) = Cells(Rng(2)(1).Row - 8, E.Column) + 1
  10.         End If
  11.     Next
  12. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# register313

register313¦Ñ®v:
·PÁ±zªº«ü¾É,­ì¨Ó¬O³o¼Ë³B²z,¨ä¹ê n4~n8 ¬O¤p§Ì¦]¬°¤£ª¾¹D¦p¦óÅý¤U¦Cµ{§Ç°õ¦æ«á

Dim C%, r%, Rng As Range
Set Rng = [F1:K1]
For C = 14 To 14
For r  = 11 To 15
If Application.CountIf(Rng, Cells(r, C)) >= 1 Then
Cells(4, C) = 1: Exit For
End If
Next r
Next C

¦b n3 ªº¦ì¸m¥[Á`,©Ò¥H¤~¨Ï¥ÎÅܳqªº¤è¦¡,µ{¦¡¤ÀªR§¹«á,¦A¥[Á`.¤£µM¥¿±`¨Ó»¡¥u»ÝÅã¥Ü n3 ªºµ²ªG§Y¥i.¤£¹LÁÙ¬O·PÁ±zªº«üÂI.«D±`ÁÂÁÂ.
·PÁ¦U¦ì¦Ñ®vªá¤ß«ä±Ð¾É

TOP

¦^´_ 3# GBKEE

GBKEE ¦Ñ®v:
±zªº¤è¦¡,¤p§Ì»Ý­nªá¨Ç®É¶¡¬ã¨s¤@¤U,¤£¹L¦n¹³¥i¥H¦Û¦æ©µ¦ù¨ì IV ªº¦ì¸m.
¦p ­è­è¦^ÂÐ register313 ¦Ñ®v,¤@¼Ë

¨ä¹ê n4~n8 ¬O¤p§Ì¦]¬°¤£ª¾¹D¦p¦óÅý¤U¦Cµ{§Ç°õ¦æ«á
Dim C%, r%, Rng As Range
Set Rng = [F1:K1]
For C = 14 To 14
For r  = 11 To 15
If Application.CountIf(Rng, Cells(r, C)) >= 1 Then
Cells(4, C) = 1: Exit For
End If
Next r
Next C
¦b n3 ªº¦ì¸m¥[Á`,©Ò¥H¤~¨Ï¥ÎÅܳqªº¤è¦¡,µ{¦¡¤ÀªR§¹«á,¦A¥[Á`.¤£µM¥¿±`¨Ó»¡¥u»ÝÅã¥Ü n3 ªºµ²ªG§Y¥i.

¥t¥~¥i¥H¦Û¦æ©µ¦ù¨ì IV ªº¦ì¸m.¯à¤£¯à³]©w¨ì CN ³o¦U¦ì¸m. ÁÂÁ±z.
·PÁ¦U¦ì¦Ñ®vªá¤ß«ä±Ð¾É

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-1-4 21:36 ½s¿è

¦^´_ 5# jonn0510
¥t¥~¥i¥H¦Û¦æ©µ¦ù¨ì IV ªº¦ì¸m.¯à¤£¯à³]©w¨ì CN ³o¦U¦ì¸m
CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪ
¦p¸ê®Æ¦³±qIV©µ¦ù¨ìCN      ¨Ï¥ÎCurrentRegion ¥i¹F¦¨ªº

TOP

¦^´_ 6# GBKEE


GBKEE ¦Ñ®v:
¶â!§Ú¥h½®Ñ¬ã¨s¤@¤U,¤£¤Ó¯à²z¸Ñ.
¤£¹LÁÙ¬OÁÂÁ±z.
·PÁ¦U¦ì¦Ñ®vªá¤ß«ä±Ð¾É

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD