CODEWITHSUNDEEP
vbafor-loopif-statement
King
King

Reputation: 451

VBA: for loop skip if cell matches another column

How can I loop trough a column and skip the rows where the cell value matches a list in another sheet?

For Counter = 2 To 5
    rst.AddNew
        rst.Fields("Title") = Sheets("Sheet1").Cells(Counter, 1).Value
        rst.Fields("Names") = Sheets("Sheet1").Cells(Counter, 2).Value
    
    Next Counter

As you can see I have a simple for loop which will loop trough row 2 to 5, but I wanted to check if the value in the cell matches what is in another tab, and if so I wanted to skip that row.

Upvotes: 0

Views: 53

Answers (1)

anefeletos
anefeletos

Reputation: 702

Let say column to check is Column E

Dim ColumnToCheck as Range
 Set ColumnToCheck=range("Sheets2!E:E")

    For Counter = 2 To 5
       
       ValueToCheck= Sheets("Sheet1").Cells(Counter, 1).Value

       If Not IsError(Application.Match(ValueToCheck, ColumnToCheck,0)) Then
            rst.AddNew
            rst.Fields("Title") = Sheets("Sheet1").Cells(Counter, 1).Value
            rst.Fields("Names") = Sheets("Sheet1").Cells(Counter, 2).Value
      End If   

     Next Counter

Upvotes: 1

Related Questions