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

[µo°Ý] ¦r¨å¨Ï¥Î°ÝÃD

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

¸ê®Æªí:
20230516_1.jpg
2023-5-16 14:01


µ²ªGªí°õ¦æ«e:
20230516_2.jpg
2023-5-16 14:02


°õ¦æµ²ªG:
20230516_3.jpg
2023-5-16 14:02



Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, Y, i&, j&, Q&
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
With ¤u§@ªí1: Arr = Range(.[L6], .[B65536].End(3)): End With
'¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Arr):  Y(Arr(i, 1)) = i: Next
'¡ô¥OArr°}¦Cªº°j°é¦C²Ä1Äæ­È·íkey,item¬Oi°j°éÅܼÆ(¥H¦r¨å¬ö¿ýÁä­È»P¦C¸¹)
With ¤u§@ªí2
   .[F7].Resize(1000, 10).ClearContents
   '¡ô¥Oµ²ªGªí²M°£Àx¦s®æµ²ªG
   Brr = Range(.[E7], .[E65536].End(3))
   '¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J°}¦C¤¤
End With
ReDim Crr(1 To UBound(Brr), 1 To 10)
'¡ô¥O«Å§iCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr,¾î¦V½d³ò¯Á¤Þ¸¹1~10
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   Q = Y(Brr(i, 1))
   '¡ô¥OQÅܼƱa¥XBrr°}¦C1ÄæÃöÁä¦r¦bY¦r¨å¸Ì¬ö¿ýªºArr°}¦C¦C¸¹
   If Q = 0 Then Crr(i, 1) = "¬dµL¦¹PN": GoTo i01
   '¡ô¦pªGQÅܼƬO 0,¥NªíArr°}¦C¸ÌµL¦¹Áä,¥u¦bµù°OÄæ¦ì°µµù°O
   For j = 1 To 10: Crr(i, j) = Arr(Q, j + 1): Next
   '¡ô³]°j°é±NArr°}¦C­È±a¤JCrr°}¦C
i01: Next
[¤u§@ªí2!F7].Resize(UBound(Crr), 10) = Crr
'¡ô¥OCrr°}¦C­È±q[¤u§@ªí2!F7]¶}©l,­Ë¤JÀx¦s®æ¤¤
Set Y = Nothing: Erase Arr, Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD