Results 1 to 4 of 4

Find and copy non-matching cells to another worksheet

Threaded View

TropicalMagic Find and copy non-matching... 08-24-2021, 12:01 PM
Trebor76 Re: Find and copy... 08-24-2021, 09:54 PM
TropicalMagic Re: Find and copy... 08-25-2021, 12:00 PM
Trebor76 Re: Find and copy... 08-25-2021, 07:21 PM
  1. #1
    Registered User
    Join Date
    01-21-2020
    Location
    Singapore
    MS-Off Ver
    Microsoft Office 365
    Posts
    7

    Find and copy non-matching cells to another worksheet

    I would like to compare cells in Worksheet 2 against Worksheet 1.

    First check for matching cells in range A for both Worksheets 1 and 2.

    Next, if there are no matches, check for matching cells in range B for both Worksheets 1 and 2, otherwise check the next cell in range A.

    If there are no matches as well, copy these non-matching cells in ranges A and B in Worksheet 2 to a new worksheet, Worksheet 3.


    Here are my worksheets' layout:

    Worksheet 1 -

    1.png

    Worksheet 2 -

    2.png

    Worksheet 3 -

    3.png


    From the screenshot of Worksheet 2, only the rows highlighted in yellow should be copied to Worksheet 3 since its values in both range A and B cannot be found in Worksheet 1's corresponding range A and B.


    Here is my code so far, but it's only checking range A for both Worksheets 1 and 2:

    ```
    Dim Cl As Range, Rng As Range, Dic As Object

    Set Dic = CreateObject("scripting.dictionary")

    With Dic
    For Each Cl In wsk1.Range("A2", wsk1.Range("A" & Rows.Count).End(xlUp))
    .Item(Cl.Value) = Empty
    Next Cl
    For Each Cl In wsk2.Range("A2", wsk2.Range("A" & Rows.Count).End(xlUp))
    If Not .Exists(Cl.Value) Then
    If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
    End If
    Next Cl
    End With

    If Not Rng Is Nothing Then
    Rng.EntireRow.Copy wsk3.Range("A" & Rows.Count).End(xlUp)
    End If
    ```

    Many thanks!
    Last edited by TropicalMagic; 08-25-2021 at 11:59 AM. Reason: Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Open 2nd workbook find matching worksheet names and copy values to 1st workbook
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-09-2017, 10:22 AM
  2. Find Matching Cells & copy/paste
    By backyardfun in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-31-2015, 02:40 PM
  3. Replies: 3
    Last Post: 11-26-2013, 04:53 AM
  4. Replies: 1
    Last Post: 11-25-2013, 09:30 AM
  5. Copy/Paste range of cells to another worksheet based on matching criteria
    By maa50904 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-05-2013, 12:07 AM
  6. Search to find matching cells and copy/transpose adjacent data to original sheet?
    By thump4r in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-12-2009, 09:20 PM
  7. Replies: 1
    Last Post: 10-27-2009, 03:59 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1