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

½Ð±Ð¤@­Ó¥ÎEXCEL«ö¶s¶ñªíªº°ÝÃD

½Ð±Ð¤@­Ó¥ÎEXCEL«ö¶s¶ñªíªº°ÝÃD

¥»©«³Ì«á¥Ñ tku0216 ©ó 2012-4-24 17:56 ½s¿è

¦U¦ì¤j¤j¦n¡A½Ð±Ð¤@­Ó¥ÎEXCEL«ö¶s¶ñªíªº°ÝÃD¡G
¦pªþ¹Ï©Ò¥Ü¡A§Ú­n«ö¶s¤§«á¡A±N¬Y¨Ç¸ê°T¶ñ¤JEXCELªí®æ(«ö¶s¸ò¸ê°T³£¤w³]­p¦n)¡A
¨C«ö¤@¤U¶s¡A¸ê®Æ´N·|¨Ì§Ç¶ñ¤Jªí®æ¡A¦ý°ÝÃD¬O¡AÃD¸¹2-20ÃD¥H«áªºµ{¦¡¸Ó«ç»ò¼g©O¡H

¸É¤WÀɮ׳sµ²¡A½Ð°Ñ¦Ò¡AÁÂÁ¡C
-----------------------------
http://db.tt/ipPHXMVM
--------------------------------
µ{¦¡¬° ¡G
With Range("i23").End(xlUp)     '±qi23¶}©l©¹¤W§PÂ_
    .Offset(1, 0) = Range("g24") '±Ng24ªº¸ê®Æ¶ñ¤Ji23
    .Offset(1, 1) = Range("f6")     '±Nf6ªº¸ê®Æ¶ñ¤Jj3
    End With

ø¹Ï1.jpg (141.62 KB)

ø¹Ï1.jpg

¦^´_ 1# tku0216

¬¡°Ê¼s§i:À°§U¦Û¤v!¤]µ¹µªÃDªÌ¤@­Ó¤è«K!
1.µo°ÝªÌ½Ð¤W¶ÇEXCELÀ£ÁYÀÉ(¤p¾Ç¥Í¤]¥i¥H)
2.°t¦XEXCELÀɽЧâ¥\¯à»¡©ú²M·¡


«ö¶s¸ò¸ê°T³£¤w³]­p¦n,¬°¦ó¤£¤W¶Ç?
­n¶ñ¤Jªº¸ê®Æ¦b¨º¨ÇÀx¦s®æ?¤£ª¾¹D? ¦p¦ó³]­p?

TOP

¦^´_ 1# tku0216

§A¬O­n¤@¦¸¶ñ¤J¤@ÃD? ¤Ó³Â·Ð¤F§a
  1. Sub aa()
  2. Set Rng = [I22]
  3. C = Application.CountA([I22], [N22], [S22], [X22], [AC22], [AH22])
  4. With Rng.Offset(0, C * 5).End(xlUp)
  5.   .Offset(1, 0) = [G24]
  6.   .Offset(1, 1) = [F6]
  7. End With
  8. End Sub
½Æ»s¥N½X

TOP

·PÁ¦^ÂСA¨ä¹ê³o¬O¤@­ÓÅý¦Ñ®vª¾¹D¦Û¤v©RÃD±¡§Îªºªí®æ¡A©Ò¥H¬O¤@¦¸¥u·|¶ñ¤J¤@ÃD¨S¿ù¡C
¥»·N¬O·í¦Ñ®v¿ï¾ÜÄÝ©Ê¡B§Þ¯à³W½d»PÃø©ö«×¤§«á¡A«ö¤U«ö¶s·|¦Û°Ê¶ñ¤J¡A´î¬Ù¤â°Ê¶ñ¼g®É¶¡¡C
¦ý¥Ø«e¦¹ªí¥u¯à¨Ì§Ç©¹¤U¶ñ¼g¡A±zªºµ{¦¡½X§Ú¥[¶i«ö¶s¡Aµo²{µLªk¹B§@¡A¥i¥H½Ð±Ð­ì¦]¶Ü¡HÁÂÁ¡C
---------------------------------------------
Private Sub CommandButton1_Click()
Set Rng = [I22]
C = Application.CountA([I22], [N22], [S22], [X22], [AC22], [AH22])
With Rng.Offset(0, C * 5).End(xlUp)
  .Offset(1, 0) = [G24]
  .Offset(1, 1) = [F6]
End With
End Sub
--------------------------------------------

TOP

¦^´_ 4# tku0216

¨S°ÝÃDªº,¦A´ú¸Õ¬Ý¬Ý(§AªºÀɮפӤj,µLªk¤W¶Ç)

1.gif

TOP

¦hÁÂregisterªº«ü±Ð¡Aµ{¦¡¹B§@¥¿±`¡A¦]¬°¬Q¤Ñ¬O¦b®a¸Ì¸Õ¡A¤£¾å±o¥X¤F¬Æ»ò°ÝÃD¡C
ÁÙ¦³·Q½Ð±Ð±zªºµ{¦¡½X¡A³o¤T¦æªº·N«ä¡AÁÂÁ¡G
--------------------------------------------
Set Rng = [I22]
C = Application.CountA([I22], [N22], [S22], [X22], [AC22], [AH22])
With Rng.Offset(0, C * 5).End(xlUp)
--------------------------------------------

TOP

¦^´_ 6# tku0216

Set Rng = [I22]
'­pºâ I22, N22, S22, X22, AC22, AH22³o6®æ¦³´X®æ¤£¬°ªÅ¥Õ
C = Application.CountA([I22], [N22], [S22], [X22], [AC22], [AH22])
'¥HI22¬°°ò·Ç,¦V¥k°¾²¾0¦C(C * 5)Äæ,¦A©¹¤W§ä²Ä¤@­Ó«DªÅ¥ÕÀx¦s®æ
With Rng.Offset(0, C * 5).End(xlUp)

1. I22=""
    C=0
    I22¦V¥k°¾²¾0Äæ(I22),¦A©¹¤W§ä²Ä¤@­Ó«DªÅ¥ÕÀx¦s®æ => ¶ñIÄæ
2. I22<>"" ¥B N22=""
    C=1
   I22¦V¥k°¾²¾5Äæ(N22),¦A©¹¤W§ä²Ä¤@­Ó«DªÅ¥ÕÀx¦s®æ => ¶ñNÄæ
3. I22<>"" ¥B N22<>"" ¥B S22=""
    C=2
    I22¦V¥k°¾²¾10Äæ(S22),¦A©¹¤W§ä²Ä¤@­Ó«DªÅ¥ÕÀx¦s®æ => ¶ñSÄæ
¥H¦¹Ãþ±À...

TOP

¦hÁ¤j¤j¤À¨É¡A§Ú§¹¥þÁA¸Ñµ{¦¡¤F¡C
·Q¦A½Ð±Ðªþ±aªº¤p°ÝÃD¡G
1.Set Rng = [I22]  '¥Nªí³]©wÀx¦s®æI22¬°°ò·Ç¡A¥H§Q«áÄòªºÄæ¦ìÂà´«¡C
´«¦¨¡§Set Rng = Range("i22")¡¨ªº·N¸q¬O§_¤@¼Ë¡H(¸Õ¹Lµ{¦¡¤@¼Ë¥i¥H¥¿½T¤u§@)

2.»Ý­n©I¥sexcel¨ç¦¡®É¡A¬O§_³£¬O¥HApplication¶}ÀY¡H

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-27 14:08 ½s¿è
  1. Private Sub CommandButton1_Click()
  2. Dim Rng As Range, A As Range, C As Range
  3. Dim k%, x%, y%, n%, s%, m$
  4. Set Rng = Range("I3:I22,N3:N22,S3:S22,X3:X22,AC3:AC22,AH3:AH22")  '¶ñ¼g°Ï°ì
  5. k = Application.CountA(Rng) '­pºâ¶ñ¼g°Ï°ì¥H¶ñ¼g¼Æ¶q
  6. x = IIf((k + 1) Mod 20 = 0, 20, (k + 1) Mod 20)  '­pºâ¼Æ¶q¨D¨ä°£¥H20ªº¾l¼Æ¡A§Y¬°±N³Q¶ñ¼gªº¦C¸¹
  7. y = Int((k - 1) / 20) + 1 + IIf(x = 1, 1, 0)  '­pºâ¥X²Ä´XÄæ±N³Q¶ñ¼g
  8. Set A = Rng.Areas(y).Cells(x)  '±N³Q¶ñ¼gªºÀx¦s®æ
  9. n = [B17:B21].Find(ComboBox1).Row  'ÄÝ©Ê
  10. s = ScrollBar2.Value + 2 'Ãø©ö«×
  11. For Each C In [C3:C6] '§Þ¯à³W½d
  12.   If C <> "" Then m = IIf(m = "", C, m & "+" & C)
  13. Next
  14. Cells(n, s) = Cells(n, s) + 1  '¼g¤J¥XÃD¼Æ¶q
  15. A.Resize(, 2) = Array(m, ComboBox1)  '¶ñ¤Jªí®æ
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Hsieh¤j¤j¡A¤£¦n·N«ä¡A¦³ÂIÃøÀ´¡A¥i§_´£¨Ñµù¸Ñ¡H
§Ú¥u¬Ý±oÀ´¡G
²Ä¤­¦æ¡G­pºâ«D¹sÀx¦s®æ¡Ak=0¡C
²Ä¤»¦æ¡Gx=iif(1 mod 20=0, 20,1), ©Ò¥Hx=1¡C
²Ä¤C¦æ¡Gy=Int((-1) / 20) + 1 + IIf(x = 1, 1, 0)??
¤§«á´N¬Ý¤£À´¤F...

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD