標題:
[發問]
更新資料但列位不同
[打印本頁]
作者:
aok669
時間:
2010-6-12 03:30
標題:
更新資料但列位不同
各位前輩好
從B欄開始是期數
將''IN''工作表的資料,匯入至''本''工作表
例如:099114期至099116期未更新資料,請抓取 ''in''工作表資料 更新
感謝
詳檔案
[attach]1226[/attach]
作者:
GBKEE
時間:
2010-6-12 06:30
回復
1#
aok669
Sub Ex()
Dim d As Object, Rng As Range, R As Range, C As Range
Set d = CreateObject("Scripting.Dictionary")
With Sheets("in")
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)
For Each R In Rng.Rows
For Each C In R.Cells
d(.Cells(C.Row, 1) & .Cells(1, C.Column)) = C
Next
Next
End With
With Sheets("本")
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)
For Each R In Rng.Rows
For Each C In R.Cells
If d.EXISTS(.Cells(C.Row, 1) & .Cells(1, C.Column)) Then C = d(.Cells(C.Row, 1) & .Cells(1, C.Column))
Next
Next
End With
End Sub
複製代碼
作者:
Hsieh
時間:
2010-6-12 07:31
這不是IN表直接複製到本表就好了嗎
作者:
GBKEE
時間:
2010-6-12 08:05
回復
3#
Hsieh
樓主的檔案 "本"工作表中有一些欄位是 "IN"工作表所沒有的 直接複製整個工作表會覆蓋掉 "本" 原有的資料
作者:
Hsieh
時間:
2010-6-12 10:29
回復
4#
GBKEE
Sub Ex()
Set d = CreateObject("Scripting.Dictionary")
With Sheet1
For Each a In .Range(.[B1], .[B1].End(xlToRight))
For Each b In .Range(.[A2], .[A2].End(xlDown))
d(a & b) = .Cells(b.Row, a.Column).Value
Next
Next
End With
With Sheet2
For Each a In .Range(.[B1], .[B1].End(xlToRight))
For Each b In .Range(.[A2], .[A2].End(xlDown))
If .Cells(b.Row, a.Column) = "" Then .Cells(b.Row, a.Column).Value = d(a & b)
Next
Next
End With
End Sub
複製代碼
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
