CODEWITHSUNDEEP
vbaexcel
Robby Stolle
Robby Stolle

Reputation: 21

VBA to compare two columns and copy row

In Excel I am trying to make a macro that checks for two criteria in a row and copy it to another page. This is what I have so far. If I take out all of the AK references it does work on the first variable but the second one is causing me grief. Any thoughts? thank you for your time in advance. 

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

Upvotes: 2

Views: 462

Answers (1)

urdearboy
urdearboy

Reputation: 14590

Your range variable ak is not working the way you think it is.

Just loop through your column AJ and check the value in AK by using Offset(,1) which will look one column over to the right. 

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

You can clean this up by combining your criteria and using a For i loop. 

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

Upvotes: 2

Related Questions