[發問] 更新資料但列位不同

aok669

各位前輩好

從B欄開始是期數

將''IN''工作表的資料,匯入至''本''工作表

例如:099114期至099116期未更新資料,請抓取 ''in''工作表資料 更新

感謝

詳檔案

updata.rar (23.48 KB)

請看
下載次數: 20

2010-6-12 03:29

Hsieh

回復 4# GBKEE

1. Sub Ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. With Sheet1
   
4.   For Each a In .Range(.[B1], .[B1].End(xlToRight))
   
5.      For Each b In .Range(.[A2], .[A2].End(xlDown))
   
6.         d(a & b) = .Cells(b.Row, a.Column).Value
   
7.      Next
   
8.   Next
   
9. End With
   
10. With Sheet2
    
11.   For Each a In .Range(.[B1], .[B1].End(xlToRight))
    
12.      For Each b In .Range(.[A2], .[A2].End(xlDown))
    
13.        If .Cells(b.Row, a.Column) = "" Then .Cells(b.Row, a.Column).Value = d(a & b)
    
14.      Next
    
15.   Next
    
16. End With
    
17. End Sub

複製代碼

GBKEE

回復 3# Hsieh
樓主的檔案 "本"工作表中有一些欄位是 "IN"工作表所沒有的 直接複製整個工作表會覆蓋掉 "本" 原有的資料

Hsieh

這不是IN表直接複製到本表就好了嗎

GBKEE

回復 1# aok669

1. Sub Ex()
   
2. Dim d As Object, Rng As Range, R As Range, C As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. With Sheets("in")
   
5.     Set Rng = .Range("B2", .Cells(1, Columns.Count).End(xlToLeft).Offset(1)).Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 1, .Cells(1, Columns.Count).End(xlToLeft).Column - 1)
   
6.     For Each R In Rng.Rows
   
7.         For Each C In R.Cells
   
8.             d(.Cells(C.Row, 1) & .Cells(1, C.Column)) = C
   
9.         Next
   
10.     Next
    
11. End With
    
12. With Sheets("本")
    
13.     Set Rng = .Range("B2", .Cells(1, Columns.Count).End(xlToLeft).Offset(1)).Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 1, .Cells(1, Columns.Count).End(xlToLeft).Column - 1)
    
14.     For Each R In Rng.Rows
    
15.         For Each C In R.Cells
    
16.             If d.EXISTS(.Cells(C.Row, 1) & .Cells(1, C.Column)) Then C = d(.Cells(C.Row, 1) & .Cells(1, C.Column))
    
17.             
    
18.         Next
    
19.     Next
    
20. End With
    
21. End Sub

複製代碼