此代碼有效，但它將值放在 Sheet2(Table2) 的底部，而不是 table2 中的下一個可用行。任何建議，將不勝感激。謝謝
https://drive.google.com/file/d/19fZ6GLGtVNd13I-GTgLVjnfKlzrwx05U/view?usp=sharing
子宏（）
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2")
Dim LastRow As Long
Dim s As Long
Dim myRow As Long
s = ws.Range("A" & Application.Rows.Count).End(xlUp).Row
LastRow = Sheets("Sheet1").Cells(Rows.Count, "I").End(xlUp).Row
For myRow = 2 To LastRow
If Sheets("Sheet1").Cells(myRow, "I") = "INACTIVE" Then
ws.Range("A" & s 1) = Sheets("Sheet1").Cells(myRow, "A")
ws.Range("B" & s 1) = Sheets("Sheet1").Cells(myRow, "B")
ws.Range("C" & s 1) = Sheets("Sheet1").Cells(myRow, "C")
ws.Range("D" & s 1) = Sheets("Sheet1").Cells(myRow, "I")
End If
Next myRow
結束子
uj5u.com熱心網友回復：
將資料從一個 Excel 表復制到另一個
- 在 VBA 中處理 Excel 表格可能非常棘手。這是一個簡單的用戶友好版本。您可以通過在回圈中使用（一組）表頭等等來更深入地研究它。
Option Explicit
Sub Macro()
Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Sheet1")
Dim stbl As ListObject: Set stbl = sws.ListObjects("Table1")
Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets("Sheet2")
Dim dtbl As ListObject: Set dtbl = dws.ListObjects("Table2")
Dim sCell As Range
Dim srrg As Range
Dim drrg As Range
Dim r As Long
For Each sCell In stbl.ListColumns("Status").DataBodyRange
r = r 1
If StrComp(CStr(sCell.Value), "INACTIVE", vbTextCompare) = 0 Then
Set srrg = stbl.ListRows(r).Range
Set drrg = dtbl.ListRows.Add.Range
drrg.Cells(1).Value = srrg.Cells(1).Value
drrg.Cells(2).Value = srrg.Cells(2).Value
drrg.Cells(3).Value = srrg.Cells(3).Value
drrg.Cells(4).Value = srrg.Cells(9).Value
End If
Next sCell
End Sub
uj5u.com熱心網友回復：
下面的代碼應該作為s變數在回圈內作業。
附言
更新了代碼以匹配您的示例。此外，sheet2 有表，所以最后一行沒有被正確檢測到End(xlUp).Row
問題還在于myRow =2
Sub Macro()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet2")
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim LastRow As Long
Dim s As Long
Dim myRow As Long
LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "I").End(xlUp).Row
For myRow = 1 To LastRow
s = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row 1
If ws1.Cells(myRow, "I") = "INACTIVE" Then
ws.Range("A" & s & ":C" & s) = ws1.Range("A" & myRow & ":C" & myRow).Value
ws.Range("D" & s) = "INACTIVE"
End If
Next myRow
MsgBox "OK"
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/442624.html
下一篇：在分隔符前后添加和修剪空格