[發問] 如何帶出指定格資料

luke

小弟有個選單上問題, 因選項內容資料變動很大, 我想利用代碼自sheet2資料區中, 帶入想要的資料至sheet1表格

麻煩各位先進!!
TEST2.rar (34.16 KB)

下載次數: 13

2012-3-30 23:29

Hsieh

回復 1# luke

1. Sub ex()
   
2. Dim ay(2, 11)
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. With sheet2
   
5. For Each a In .Range(.[A2], .[A2].End(xlDown))
   
6.    d(a.Value) = a.Offset(, 1).Value
   
7. Next
   
8. End With
   
9. With sheet1
   
10. .[B1:k1].Copy .[B14]: r = 15
    
11. For Each a In .Range(.[A2], .[A2].End(xlDown))
    
12.   ar = a.Resize(, 11).Value
    
13.   For i = 1 To 11
    
14.     ay(0, i - 1) = ar(1, i)
    
15.     ay(1, i - 1) = d(ar(1, i))
    
16.   Next
    
17.   .Cells(r, 1).Resize(2, 11) = ay
    
18.   r = r + 3
    
19.   Erase ay
    
20. Next
    
21. End With
    
22. End Sub

複製代碼

luke

回復 2# Hsieh


測試OK

謝謝H大