這是我的帖子的后續：VBA Macro to replace data based on matching ID columns。
因此，我現在繼續將代碼與我的實時環境資料一起使用，該資料由數百行和十列資料組成。我的問題是在我的“學習者電子表格”中，我只需要從大資料庫中提取某些資料列。
代碼如下：
Sub Button1_Click()
Dim OpenFileName As String
Dim wb As Workbook
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim m, rw As Range
OpenFileName = Application.GetOpenFilename 'Select and Open workbook
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName, ReadOnly:=True)
Set wsCopy = wb.Worksheets(1) 'for example
Set wsDest = Workbooks("Learner data Elliot.xlsx").Worksheets(1)
'Learner Details
For Each rw In wsCopy.Range("B2:F" & wsCopy.Cells(Rows.Count, "B").End(xlUp).Row).Rows
'matching row based on Id ?
m = Application.Match(rw.Cells(1).Value, wsDest.Columns("A"), 0)
'if we didn't get a match then we add a new row
If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'new row
rw.Copy wsDest.Cells(m, "A") 'copy row starting from column A
Next rw
'Programme Details
For Each rw In wsCopy.Range("S2:T" & wsCopy.Cells(Rows.Count, "B").End(xlUp).Row).Rows
'matching row based on Id ?
m = Application.Match(rw.Cells(1).Value, wsDest.Columns("A"), 0)
'if we didn't get a match then we add a new row
If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'new row
rw.Copy wsDest.Cells(m, "I") 'copy row starting from column I
Next rw
'wb.Close False no save
MsgBox ("Done")
End Sub
代碼的作業方式是從主資料庫中獲取列 B2:F 中的資料，然后將 ID 與學習者資料庫進行比較。如果 ID 匹配，它將用新匯入的資料覆寫 B2:F 中的資料，如果 ID 不匹配，它將在學習者資料庫中創建一個新行并填充這 5 列。
第一個 For Each 回圈作業得很好。但是，現在我需要在主資料庫中跳轉并從 S2:T 列復制并將它們放入我的學習者資料庫中的 I:J 列中。
第二個 For Each 回圈中出現問題的主要原因是，因為我從 S2 開始，它不像在第一個回圈中那樣從 B2 讀取 ID，這意味著它無法匹配 ID，因此正在創建一個一堆帶有 I:J 資料的新行。
無論如何將回圈合并在一起，以便我可以同時顯示來自 B2:F 和 S2:T 的資料？我花了幾個小時嘗試不同的方法，但我似乎沒有嘗試過任何作業，所以如果有人能幫助我或至少指出我正確的方向，我將不勝感激。
謝謝！
更新
Based off of the comment from @Apostolos55, I'd like to try and offset "S2" by (0, -17). However, when trying to implement this into my for each loop in my code I have been struggling to get it to work. Would anyone be able to help either demonstrate using my current code above or point me in the right direction as to how I might be able to get the outcome I desire?
For Each rw In wsCopy.Range("S2:T" & wsCopy.Cells(Rows.Count, "B").End(xlUp).Row).Offset(0, -17).Rows
'matching row based on Id?
m = Application.Match(rw.Cells(1).Value, wsDest.Columns("A"), 0)
'if we didn't get a match then we add a new row
If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'new row
rw.Copy wsDest.Cells(m, "I") 'copy row starting from column I
Next rw
I have added Offset(0, -17) to the code as shown above, which is returning a match but it is copying the data from B2 instead of S2.
uj5u.com熱心網友回復：
我設法通過創建一個新的作業表來以正確的順序對列進行排序來解決這個問題，以避免不得不處理這個問題。
再次感謝所有評論的人。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/345287.html