Looking for cells which contain certain value in another sheet

user_16287035 0
Viewed 47

I have two sheets where Sheet 1 has three vendor number in column A and Sheet 2 has a list of account names in column B.

I would like to search in Sheet 1 column A all cells where the value contains the cell value in Sheet 2 column C. For example, if the cells from C2:C5 contain one of the value between A2:A4, Sheet 2 cells from E2:E5 will show "Yes" else showing nothing.

Below are my codes, but I keep getting an error message:

"Invalid Next control variable reference".

Set sh1 = Worksheets("Vendor")
Set sh2 = Worksheets("Invoice")
Set r1 = sh1.Range("A2:A4")
Set sh1 = Worksheets("Vendor")
Set sh2 = Worksheets("Invoice")
Set r1 = sh1.Range("A2:A4")
Set r2 = sh2.Range("E2:E5")

For Each vendorCell In r2
    If cell = vendorCell Then
        cell = "Yes"
    Else
        cell = "out of range"
        Exit For
    End If
Next cell
vba excel
1 Answers

I would like to search in Sheet 1 column A all cells where the value contains the cell value in Sheet 2 column C. For example, if the cell from 'C2' to 'C5'contains one of the value between "A2" and "A4", sheet 2 cell from 'E2' to 'E5' will show "Yes" else showing nothing.

Your loop says For Each vendorCell In r2 but then you are closing it with Next cell and hence you are getting that error. It should be Next vendorCell.

Having said that, I think this is what you are trying to achieve?

Option Explicit

Sub Sample()
    Dim sh1 As Worksheet: Set sh1 = Worksheets("Vendor")
    Dim sh2 As Worksheet: Set sh2 = Worksheets("Invoice")
    
    Dim r1 As Range: Set r1 = sh1.Range("A2:A4")
    Dim r2 As Range: Set r2 = sh2.Range("C2:C5")
    
    Dim aCell As Range, bCell As Range
    
    '~~> Loop through col C
    For Each aCell In r2
        aCell.Offset(, 2).Value = "out of range"
        
        '~~> Loop through column A
        For Each bCell In r1
            If aCell.Value2 = bCell.Value2 Then
                '~~> Write to column E
                aCell.Offset(, 2).Value = "Yes"
                Exit For
            End If
        Next bCell
    Next aCell
End Sub

Note:

  1. This is a very small range so using For Next is ok. Otherwise I would have recommended using .Find
  2. BTW you do nto need VBA for this. You can achieve this using formulas as well :)
user_11405790
Related