none
Search values between sheets of same workbook uisng VBA RRS feed

  • Question

  • Hi All,

    Background:

    I have an excel file with two sheets namely 'sheet1' and 'sheet2'. Both the sheets have same headers.

    Sheet1- header starts from columnB and Sheet2- from columnA.

    The first Header(in both sheets) is<UID> unique ID.

    Both the columns in respective sheets has an array of values

    Problem:

    How do I search if the values in sheet2(columnA) are present in sheet1 (columnB) using VBA..?

    My theoritical procedure:

    Loop until UID in 'Sheet1' is empty

    1. Go to 'sheet2'
    2. Read UID value
    3. Go to 'sheet1'
    4. Search in UID column for read UID
    5. If found

           'some operations

    6. If not found

           'some operations

    Loop end

    Please guide me on how can I do this search activity.

    Thanks in advance!


    • Edited by Srinidhi Laks Monday, February 9, 2015 10:44 AM It has to be looped
    Monday, February 9, 2015 10:24 AM

Answers

  • Sub test()
      Dim UID As Range, R As Range
      
      With Sheets("sheet2")
        'for each UID in A2:A?
        For Each UID In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
          'search in sheet1
          Set R = Sheets("sheet1").Range("A:A").Find(UID.Value, LookIn:=xlValues, LookAt:=xlWhole)
          If R Is Nothing Then
            'not found
          Else
            'found
          End If
        Next
      End With
    End Sub
    

    Monday, February 9, 2015 2:42 PM