excel - 对两列执行检查，逐列复制另一个工作表

Question

我正在尝试创建一种方法来检查空白范围内的特定列。然后，如果列不为空，我想将该行中的所有值复制到另一个工作表。
现在我完全被困住了。任何人都可以给出一些提示如何继续循环以达到如下截图所示的结果？
在下面的示例中，我需要复制:

从 B 列第 19、20、22 行到另一个工作表 A 列(从 A8 开始)

从 C 列第 19、20、22 行到另一个工作表 B 列(从 B8 开始)

从 E 列第 20、22 行到另一个工作表 C 列(从 C9 开始)

从 F 列第 20、22 行到另一个工作表 D 列(从 D9 开始)

从 G 列第 20、22 行到另一个工作表 E 列(从 E9 开始)

在我当前的代码中，如果语句基本上不能作为输出工作，我会将所有行都放到调试窗口中。
这是代码(方法):

Sub CopyTimeScheduleMethod(SearchWordOne As String, SearchWordTwo As String, RowToPaste As Integer, OperatingWorksheet As Worksheet)

    Dim FirstWord, SecondWord
    Dim cell As Range
    Dim rng As Range
    Dim row As Range
            
    Set FirstWord = OperatingWorksheet.Range("A:A").Find(SearchWordOne, LookIn:=xlValues, lookat:=xlWhole)
    Set SecondWord = OperatingWorksheet.Range("A:A").Find(SearchWordTwo, LookIn:=xlValues, lookat:=xlWhole)
    
    Set rng = OperatingWorksheet.Range(OperatingWorksheet.Cells(FirstWord.row + 2, FirstWord.Column), _
OperatingWorksheet.Cells(SecondWord.row - 3, FirstWord.Column))
    
    ThisWorkbook.Worksheets("Timeschedule").Range("A8:G90").ClearContents

    For Each row In rng.Rows
      For Each cell In row.Cells
        If IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=21).Value) And _
        Not IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=1).Value) Then
            Debug.Print cell.Offset(rowOffset:=0, columnOffset:=1).Value
        End If
      Next cell
    Next row

End Sub

作为输出，我得到:

1.1
1.1.2.1

如何构建我的 If 语句以具有 1 Task以及第一列和第二列(B 和 C)列，但将其从其他列(E、F 和 G)中排除？
这是 rng.Copy 上的范围(从这里我需要复制):

这是它在另一个工作表上的样子:


编辑:
这现在可以正常工作，但是第一个“标题”行仍然存在问题。怎么抓？一种选择当然是从源工作表的第五列中删除“选择”。

Sub CopyTimeScheduleMethod(SearchWordOne As String, SearchWordTwo As String, RowToPaste As Integer, OperatingWorksheet As Worksheet)

    Dim FirstWord, SecondWord
    Dim cell As Range
    Dim rng As Range
    Dim row As Range
    Dim x
            
    Set FirstWord = OperatingWorksheet.Range("A:A").Find(SearchWordOne, LookIn:=xlValues, lookat:=xlWhole)
    Set SecondWord = OperatingWorksheet.Range("A:A").Find(SearchWordTwo, LookIn:=xlValues, lookat:=xlWhole)
    
    Set rng = OperatingWorksheet.Range(OperatingWorksheet.Cells(FirstWord.row + 2, FirstWord.Column), _
    OperatingWorksheet.Cells(SecondWord.row - 3, FirstWord.Column))
    
    ThisWorkbook.Worksheets("Timeschedule").Range("A8:G90").ClearContents
    
    x = 8

    For Each row In rng.Rows
      For Each cell In row.Cells
        If IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=21).Value) And _
        Not IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=2).Value) Then
            Debug.Print cell.Offset(rowOffset:=0, columnOffset:=1).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("A" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=1).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("B" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=2).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("C" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=4).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("D" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=5).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("E" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=6).Value
             
             x = x + 1
             
        End If
      Next cell
    Next row

End Sub

基本上还有待解决的是如何在哪里制作 If 语句:

C列中的单元格不为空

V列中的单元格不为空或不是“OPT”

If IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=21).Value) And _
cell.Offset(rowOffset:=0, columnOffset:=21).Value <> "OPT" And _
Not IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=2).Value) Then

Answer 1

这是工作解决方案:

Sub CopyTimeScheduleMethod(SearchWordOne As String, SearchWordTwo As String, RowToPaste As Integer, OperatingWorksheet As Worksheet)

    Dim FirstWord, SecondWord
    Dim cell As Range
    Dim rng As Range
    Dim row As Range
    Dim x As Integer
            
    Set FirstWord = OperatingWorksheet.Range("A:A").Find(SearchWordOne, LookIn:=xlValues, lookat:=xlWhole)
    Set SecondWord = OperatingWorksheet.Range("A:A").Find(SearchWordTwo, LookIn:=xlValues, lookat:=xlWhole)
    
    Set rng = OperatingWorksheet.Range(OperatingWorksheet.Cells(FirstWord.row + 2, FirstWord.Column), _
    OperatingWorksheet.Cells(SecondWord.row - 3, FirstWord.Column))
    
    ThisWorkbook.Worksheets("Timeschedule").Range("A8:G90").ClearContents
    
    x = 8

    For Each row In rng.Rows
      For Each cell In row.Cells
        If Not IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=2).Value) And _
        (cell.Offset(rowOffset:=0, columnOffset:=21).Value = "OPT" Or _
        IsEmpty(cell.Offset(rowOffset:=0, columnOffset:=21).Value)) Then
            'Debug.Print cell.Offset(rowOffset:=0, columnOffset:=1).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("A" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=1).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("B" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=2).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("C" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=4).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("D" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=5).Value
             
             ThisWorkbook.Worksheets("Timeschedule").Range("E" & x).Value _
             = cell.Offset(rowOffset:=0, columnOffset:=6).Value
             
             x = x + 1
             
        End If
      Next cell
    Next row

End Sub