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

­ì¸ê®Æ¦p¦ó¥Î¦r¨å©î¤À¡A¤J¤£³sÄòªºÄæ¦ì

­ì¸ê®Æ¦p¦ó¥Î¦r¨å©î¤À¡A¤J¤£³sÄòªºÄæ¦ì

½Ð°Ý³o¤@¬q¦p¦ó§ï
Sub test() '¦r¨å»P¼Æ²Õ
Dim arr
    Set d = CreateObject("scripting.dictionary")
    Dim lastrow&
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    arr = Sheet3.Range("a1:h" & lastrow)
    For i = 2 To UBound(arr)
'        d(arr(i, 1)) = Array(arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5), arr(i, 6), arr(i, 7), arr(i, 8))
        d(arr(i, 1)) = Array(arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 6), arr(i, 7), arr(i, 8))
    Next
    For Each Rng In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
        Rng.Offset(0, 1).Resize(1, 8) = d(Rng.Value)
    Next
End Sub

¦r¨å1070913.zip (12.99 KB)

§Æ±æ¤ä«ù!

¦^´_ 1# s7659109
½Ð°Ñ¦Ò
  1. Sub test() '¦r¨å»P¼Æ²Õ
  2.     Dim arr
  3.     Set d = CreateObject("scripting.dictionary")
  4.     Dim lastrow&
  5.     lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
  6.     arr = Sheet3.Range("a1:h" & lastrow)
  7.     For i = 2 To UBound(arr)
  8. '        d(arr(i, 1)) = Array(arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5), arr(i, 6), arr(i, 7), arr(i, 8))
  9.         d(arr(i, 1)) = Array(arr(i, 2), arr(i, 3), "", arr(i, 7), arr(i, 8), "", arr(i, 4))
  10.     Next
  11.     For Each Rng In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
  12.         Rng.Offset(0, 1).Resize(1, 7) = d(Rng.Value)
  13.     Next
  14. End Sub
½Æ»s¥N½X

TOP

¦pªG ¥u¬O­nÄæ¦ì¶×¤J¡A¤£§PÂ_¡A¤U­±­n¦p¦ó§ï¡H

For Each Rng In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
        Rng.Offset(0, 1).Resize(1, 7) = d(Rng.Value)
    Next
§Æ±æ¤ä«ù!

TOP

§ï¦¨³o¼Ë¥i¹F¦¨²Ä2°ÝÃD¡A¦ý¦³§ó¦nªº¶Ü¡H
Sub test()
Dim arr1, arr2, r, rr
    With Sheets("sheet1")
            r = .Cells(.Rows.Count, 1).End(3).Row
            arr1 = .Range("a1:H" & r)
            arr2 = .Range("i1:q" & r)
    End With
            rr = Cells(Rows.Count, 1).End(3).Row
            Range("A2:h" & rr).ClearContents
            Range("R2:Z" & rr).ClearContents
            [A1].Resize(r, 8) = arr1
            [r1].Resize(r, 9) = arr2
End Sub
§Æ±æ¤ä«ù!

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-9-29 03:29 ½s¿è

¦^´_ 4# s7659109

§Ú¼gªº¤ñ¸ûªø¤@¨Ç¡A¦ý¬O§ó¦³"¼u©Ê"¡A¾Ç¸¹¤£³sÄò¤]µL©Ò¿×
  1. Sub test()
  2. Dim tt$, Rn&, Cn&, Ri&, Ci&
  3. Dim ¸ê®Æ½d³ò As Range, ¶ñ¼g½d³ò As Range
  4. Set d = CreateObject("scripting.dictionary")
  5. Sheets("data").Activate
  6. Rn = Cells(Rows.Count, 1).End(xlUp).Row
  7. Cn = Cells(1, Columns.Count).End(xlToLeft).Column
  8. Set ¸ê®Æ½d³ò = [b2].Resize(Rn - 1, Cn - 1)
  9. '¿é¤J¸ê®Æ¨ì¦r¨å
  10. For Each rg In ¸ê®Æ½d³ò
  11.   tt = Cells(1, rg.Column).Value
  12.   tt = tt & "," & Cells(rg.Row, 1).Value
  13.   d(tt) = rg.Value
  14. Next
  15. '==========§Ú¬O¤À®æ½u==========
  16. Sheets("test").Activate
  17. Ri = Cells(Rows.Count, 1).End(xlUp).Row
  18. Ci = Cells(1, Columns.Count).End(xlToLeft).Column
  19. Set ¶ñ¼g½d³ò = [b2].Resize(Ri - 1, Ci - 1)
  20. '±q¦r¨å¿é¥X¸ê®Æ
  21. For Each rg In ¶ñ¼g½d³ò
  22.   tt = Cells(1, rg.Column).Value
  23.   tt = tt & "," & Cells(rg.Row, 1).Value
  24.   rg.Value = d(tt)
  25. Next
  26. End Sub
½Æ»s¥N½X
¨Ï¥Î数组©M¦r¨å1070929.rar (13.22 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-9-29 03:43 ½s¿è

¦^´_ 5# n7822123

ªøÂkªø¡A¦ý¬OÀ³¸Ó«Ü¦n²z¸Ñ
¦]¬°§AªºÀɮ׬O·sª©ªº".xlsm"¡A
¤£µM·Ç¤jÀ³¸Ó¦­´N¦^§A¤F¡A

³o¸Ì«Ü¦h°ª¤â³£ÁÙ¦b¥Îª©excel¡A
¤U¦¸­n§ÖÂI±o¨ìµª®×®É¡A«Øij¤W¶ÇªºÀÉ®×¥Î".xls"
¥H¤W¬O¤ß±o¤À¨É
:D

¥t¦sª©excel ¦p¤U¡A³o¼Ë´N¦³§ó¦h¤H¥[¤J°Q½×!
¨Ï¥Î数组©M¦r¨å1070929.rar (8.73 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-9-29 09:44 ½s¿è

Sub test2()
Dim R&, Ar, Br, xA As Range
Set xA = [data!a1]
R = xA(Rows.Count, 1).End(xlUp).Row - 1
Ar = Array(2, 3, 5, 6, 8) '¨Ó·½¸ê®Æ­n½Æ»sªº[Äæ¦ì]¶¶§Ç
Br = Array(2, 3, 7, 8, 4) '­n¶K¤Jªº[Äæ¦ì]¶¶§Ç
For i = 0 To UBound(Ar)
    Cells(2, Ar(i)).Resize(R) = xA(2, Br(i)).Resize(R).Value
Next i
End Sub

­ìÃD¬O­n¥Î[¦r¨å], ¬Ý¬Ý»Ý¨D, À³¸Ó¤£¥Î¦r¨å§Y¥i,
¥i¯à¥t¦³¥Î·N, ©Ò¥H¬Ý¤FÃD´Nºâ¤F~~

TOP

ÁÂÁ¤G¦ì¤j¤jÀ°¦£¡A°}¦C»P¦r¨å¨â¤j§Q¾¹¡A¯uªº­nªá®É¶¡¤~¦³¿ìªk§l¦¬¡C
§Æ±æ¤ä«ù!

TOP

Set xA = [data!a1]
For i = 0 To UBound(Ar)
    Cells(2, Ar(i)).Resize(R) = xA(2, Br(i)).Resize(R).Value
Next i
XA ªº¥Îªk¥i§_½Ð­ã¤j¸ÑÄÀ¡H
§Æ±æ¤ä«ù!

TOP

¦^´_ 9# s7659109


xA(2, Br(i))  µ¥¦P  xA.CELLS(2, Br(i))

TOP

        ÀR«ä¦Û¦b : Ãø¦æ¯à¦æ¡AÃø±Ë¯à±Ë¡AÃø¬°¯à¬°¡A¤~¯àª@µØ¦Û§Úªº¤H®æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD