CODEWITHSUNDEEP
excelvba
Leedo
Leedo

Reputation: 611

How to check If every Cell in Range found on another Range using Excel VBA?

I have data on Worksheets("North").Range("B3:B500") , and data on Worksheets("Auto").Range("A2:A22") in the same workbook. How to check If every Cell in first Range found on the second Range, and if it exists, some code will run (Offset )?

The problem on the below code in this line: If Cell.value = Worksheets("Auto").Range("A2:A22") Then

Sub Check_Range()

Dim WorkOrder As Range
Dim Closed_Data As Range
Dim Cell As Object

Set WorkOrder = Worksheets("North").Range("B3:B500")

Set Closed_Data = Worksheets("Auto").Range("A2:A22")

     For Each Cell In WorkOrder
    
        If Cell.value = Worksheets("Auto").Range("A2:A22") Then
        
         Cell.Offset(, 6).value = "Close"
         Cell.Offset(, 7).value = Now

        End If
    Next Cell
    
End Sub

Upvotes: 0

Views: 563

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

It is not possible to compare the value of one cell to a range. Try this code:

Sub Check_Range()

    Dim WorkOrder As Range
    Dim Closed_Data As Range
    Dim Cell As Object
    
    Set WorkOrder = Worksheets("North").Range("B3:B500")
    Set Closed_Data = Worksheets("Auto").Range("A2:A22")
        
    On Error Resume Next
    For Each Cell In WorkOrder
        With Cell
            WorksheetFunction.Match .Value, Closed_Data, 0  'value search
            If Err.Number = 0 Then  ' if there is no error, then the value was found
                .Offset(, 6).Value = "Close"
                .Offset(, 7).Value = Now
            End If
            Err.Clear
        End With
    Next Cell
    On Error GoTo 0
End Sub

Upvotes: 1

Related Questions