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

[µo°Ý] ¦p¦ó¦Û­q«þ¨©¦¸¼Æ

[µo°Ý] ¦p¦ó¦Û­q«þ¨©¦¸¼Æ

¦U¦ì¤j¤j

¤p§Ì§â±`¥Î¸ê®Æ§@¦¨¸ê®Æ¦C¿ï³æ©ñ¦bsheet2ªí¸ê®Æ°Ï,
§Ú·Q¦bsheet1ªíB1¦ÜB5¥h³]©w«þ¨©¦¸¼Æ,µM«á½Æ»s¶K¤W

·Ð½Ð¥ý¶i«ü¾É
TEST5.rar (36.82 KB)

¦^´_ 1# luke
  1. Sub nn()
  2. With sheet2
  3. Set Rng = .Range(.[A1], .[A1].End(xlDown))
  4. k = 11
  5. For Each a In sheet1.[B1:B5]
  6. mystr = "SUMPRODUCT((left(sheet2!" & Rng.Address & ",2)=""" & a.Offset(, -1) & """)*1)"
  7. Set c = .Columns("A:A").Find(a.Offset(, -1), after:=.[A65536], lookat:=xlPart)
  8. r = Evaluate(mystr)
  9. For i = 1 To a
  10.    c.Resize(r, 10).Copy sheet1.Cells(k, 1)
  11.    k = k + r
  12. Next
  13. Next
  14. End With
  15. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# luke

ªþÀɤ¤,¥\¯à¸Ñ»¡«D±`²M·¡,µ¹§A«ö¤@­ÓÆg
  1. Sub Macro()
  2. RX = 0
  3. sheet1.[A11:J65536].Clear
  4. For r = 1 To 5
  5.   N = 0
  6.   Do While N < sheet1.Cells(r, 2)
  7.    sheet2.Range("A" & r * 4 - 3).Resize(4, 10).Copy sheet1.[A11].Offset(RX, 0)
  8.    N = N + 1
  9.    RX = RX + 4
  10.   Loop
  11. Next r
  12. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# register313

´ú¸ÕOK
«D±`ÁÂÁ  register313 ©MºÙÆg

PS: §Ú·Q±Nsheet2¤¤AA-EE¸ê®Æ°Ï¹ê»Ú¦C¼Æ§@­×§ï,¦pAA¸ê®Æ°Ï3¦C,BB¸ê®Æ°Ï5¦C,¨ä¥L°²³]¦U¬°4¦C¤£ÅܦpªþÀÉ,
·Ð½Ð¤j¤j«ü¾É

TEST5AA.rar (43.35 KB)

TOP

¦^´_ 2# Hsieh


    ´ú¸ÕOK   
    ÁÂÁÂHª©¤j

TOP

¦^´_ 4# luke

Hsieh¶Wª©#2¤§µ{¦¡§Y¬O:ª½±µ§PÂ_SHEET2 AÄ榳´X­ÓAA,´X­ÓBB...,§@¬°¦C¼Æ¤§§PÂ_
­Y¸ê®Æ°Ï¤§¸ê®Æ½T¬°AA01 AA02 AA03...BB01 BB01 BB03....¤§³W«h
½Ð¨Ï¥ÎHsieh¶Wª©#2¤§µ{¦¡§Y¥i

­Y¸ê®Æ°Ï¤§¸ê®ÆµL³W«h,«h¥i¨Ï¥Î¤U¦Cµ{¦¡,¦ýSHEET1 D1~D5¥²¶·¿é¤J
  1. Sub Macro()
  2. RX = 0
  3. Y = 1
  4. Sheet1.[A11:J65536].Clear
  5. For r = 1 To 5
  6.   N = 0
  7.   Do While N < Sheet1.Cells(r, 2)
  8.    Sheet2.Range("A" & Y).Resize(Cells(r, "D"), 10).Copy Sheet1.[A11].Offset(RX, 0)
  9.    N = N + 1
  10.    RX = RX + Cells(r, 4)
  11.   Loop
  12.   Y = Y + Cells(r, 4)
  13. Next r
  14. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# register313
   
ÁÂÁ  register313, µ{¦¡´ú¸ÕOK

PS: Hsieh¶Wª©ªº#2µ{¦¡SHEET2¤¤ AÄ榳¨Ç°ÝÃD,
1.­Y¸ê®Æ°ÏµLAA,BB...EEÃöÁä¦r·|¥X²{"¨S¦³³]©wª«¥óÅܼƩÎWith°Ï¶ôÅܼÆ"
2.­YAÄ椤¥ô¤@Àx¦s®æ¦³"ªÅ¥Õ"Àx¦s®æ·|¥X²{¿ù»~¨ÃÅã¥Ü400
3.AÄæ­Y¥ô¤@¸ê®Æ°Ï©Ò¹ïÀ³¸Ó°Ï³Ì«á¤@¦C¤£¬OÃöÁä¦r, ¸Ó¦C±NµLªk«þ¨©¸ê®Æ.

¥H¤W»¡©ú

TOP

¦^´_ 4# luke
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Range, xf As Range, xi As Integer
  4.     With Sheet1
  5.         .[A11:J65536].Clear
  6.         For Each E In Sheet1.[a1:a5]
  7.             Set xf = Sheet2.[A:A].Find(E, lookat:=xlPart, After:=Sheet2.[A65536])
  8.             Set xf = xf.Resize(E.Cells(1, 4), 10)
  9.             For xi = 1 To E.Cells(1, 2)
  10.                 With .Cells(Rows.Count, "A").End(xlUp).Offset(1)
  11.                     If .Row < 11 Then xf.Copy Sheet1.[a11] Else xf.Copy .Cells
  12.                 End With
  13.             Next
  14.         Next
  15.     End With
  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 7# luke
  1. Sub ex()
  2. Dim Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With sheet2
  5.   For Each a In .Range(.[A1], .[A65536].End(xlUp))
  6.      If IsEmpty(d(Left(a, 2))) Then
  7.        Set d(Left(a, 2)) = a.Resize(, 10)
  8.        Else
  9.        Set d(Left(a, 2)) = Union(d(Left(a, 2)), a.Resize(, 10))
  10.     End If
  11. Next
  12. End With
  13. r = 11
  14. With sheet1
  15. .Range("A11").CurrentRegion.Clear
  16.   For Each a In .[B1:B5]
  17.   If d.exists(a.Offset(, -1).Value) = True Then
  18.       For i = 1 To a
  19.         d(a.Offset(, -1).Value).Copy .Cells(r, 1)
  20.         r = .Cells(11, 1).End(xlDown).Row + 1
  21.       Next
  22.   End If
  23.   Next
  24. End With
  25. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# GBKEE

µ{¦¡´ú¸ÕOK

¤p§Ì­ì¥»ºc«ä¬O±qsheet2ªí¥h¦Û­q5-10­Ó¸ê®Æ°Ï·í§@µæ³æ, ¨C­Ó¸ê®Æ°Ï¦³10Äæ(A:J)¥H3-5¦C¬°¤A°Ï§Y¨C¦C10­ÓÀx¦s®æ*¦C¼Æ (¨Ò¦p: AA¸ê®Æ°Ï©Ò¥NªíAA01-AA30¶È¬°¸Ó¸ê®Æ°Ï©Ò¦b¥N¸¹¦ì¸m), ¤]´N¬Osheet2ªíAÄ椤¦³¥i¯à¬OªÅ¥Õ, ¤å¦r¤½¦¡¨Óªí¥Ü.

¥H¤U¬O´ú¸Õ(¨£²Ä1ÂI)»¡©ú:
1.°²³]sheet2ªí(ÀÉ®×TEST5AA.rar), AÄæBB¸ê®Æ°Ï¦³5¦C,­Y§âBB21©MBB41§YA6©MA8¦P®É§ï¬°ªÅ¥Õ, «þ¨©¹Lµ{¤¤¥u·|¥X²{4¦C¸ê®Æ¦C, µLªk«þ¨©5¦C¸ê®Æ¦C.

2.¤p§ÌÂà­zµ¹register313¤¤©Ò´£²Ä3ÂI¡§AÄæ­Y¥ô¤@¸ê®Æ°Ï©Ò¹ïÀ³¸Ó°Ï³Ì«á¤@¦C¤£¬OÃöÁä¦r, ¸Ó¦C±NµLªk«þ¨©¸ê®Æ¡¨¬O«üHª©¤j©Òµ¹²Ä1¦¸µ{¦¡¦³ÃöÁä¦r­­¨î,¨Ò¦p§R°£¦p«e¶µA8Àx¦s®æ·|³y¦¨µLªk«þ¨©¸Ó°Ï(§Ysheet2ªí²Ä8¦C)¸ê®Æ±¡§Î.

3.§Ú·Q­nªºµ²ªG: ¦U¸ê®Æ°Ï¤£ºÞ¬O¦óºØ®æ¦¡¤£»Ý¥h¦Ò¼{ÃöÁä¦r, ¥u»Ý¥Hsheet2ªí¦U¸ê®Æ°Ï¹ê»Ú¦C¼Æ¨Ó«þ¨©§Y¥i.

ÁÂÁÂGBKEE ª©¤j

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD