VBA比较两列并复制行

Question

在 Excel 中，我试图创建一个宏来检查连续的两个条件并将其复制到另一个页面。这是我到目前为止所拥有的。如果我取出所有 AK 引用，它确实对第一个变量起作用，但第二个变量让我感到悲伤。有什么想法吗？感谢您提前抽出时间。

Sub GenMonthWeekend()
Dim aj As Range
Dim ak As Range
Dim abc As Integer
Dim Source As Worksheet
Dim Target As Worksheet

' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Month Raw Data")
Set Target = ActiveWorkbook.Worksheets("Month Volume - Weekend")

abc = 2     ' Start copying to row 2 in target sheet
For Each aj In Source.Range("aj1:ak10000")   ' Do 10000 rows
    If aj = "1" And ak = "False" Then ' If colum aj is 1 and ak false copy
       Source.Rows(aj.Row).Copy Target.Rows(abc)
       abc = abc + 1
ElseIf aj = "7" And ak = "False" Then ' If colum aj is 7 and ak false copy
       Source.Rows(aj.Row).Copy Target.Rows(abc)
       abc = abc + 1
    End If
Next aj
End Sub

Answer 1

您的范围变量ak没有按照您想象的方式工作。

只需循环遍历您的列 AJ 并使用 Offset(,1) 检查 AK 中的值，这将查看一列正确的。

abc = 2
For Each aj In Source.Range("AJ1000")
    If aj = "1" And aj.offset(,1) = "False" Then
       Source.Rows(aj.Row).Copy Target.Rows(abc)
       abc = abc + 1
ElseIf aj = "7" And aj.offset(,1) = "False" Then
       Source.Rows(aj.Row).Copy Target.Rows(abc)
       abc = abc + 1
    End If
Next aj

<小时/>

您可以通过组合您的条件并使用 For i 循环来清理此问题。

Sub GenMonthWeekend()
Dim i As Range

Dim Source As Worksheet: Set Source = ActiveWorkbook.Worksheets("Month Raw Data")
Dim Target As Worksheet: Set Target = ActiveWorkbook.Worksheets("Month Volume - Weekend")

For i = 2 To 1000
    If (Source.Range("AJ" & i) = 1 Or Source.Range("AJ" & i) = 7) And Source.Range("AJ" & i).Offset(, 1) = "False" Then
        Source.Rows(i).Copy Target.Rows(Target.Range("A" & Target.Rows.Count).End(xlUp).Offset(1).Row)
    End If
Next i

End Sub