+ Reply to Thread
Results 1 to 5 of 5

VBA to compare data in 2 sheets, issues with existing code

  1. #1
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

    VBA to compare data in 2 sheets, issues with existing code

    Hi All,

    Found some code from an old post (http://www.excelforum.com/excel-prog...ml#post3701361) which compares data in sheet1 & sheet2 and highlights differences in both sheets.

    Applied below code to my sample file (attached) but picked up some limitations which I was hoping someone could help me with:
    - Code seems to stop comparing any further once it reaches a blank cell
    - Compares dates and values relatively well but not text columns like people's names, department names, etc.
    - There also seems to be some inconsistency in comparing which i can't quite figure out

    Any help would be greatly appreciated.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Gti182; 05-20-2014 at 02:34 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: VBA to compare data in 2 sheets, issues with existing code

    Try this...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 05-19-2014 at 09:30 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

    Re: VBA to compare data in 2 sheets, issues with existing code

    Alphafrog that is an amazing bit of code. So tidy and efficient works perfectly, thanks so much.

    Do you mind commenting what it does exactly, e.g. what exactly is the Ubound and scripting dictionary function?

    many thanks

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: VBA to compare data in 2 sheets, issues with existing code

    You're welcome.

    In the VBA Editor, highlight the UBound term and press the F1 key for a description.

    The Scripting.Dictionary doesn't have VBA help. It's a special array that has a very fast method to compare Records. It's used in this case to store the 1st sheet ID's (Refs) along with their row numbers (i). Then the 2nd sheet's ID's are compared to the Dictionary to find matches. Matched rows (i and k) are then compared cell by cell (j = columns).

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    469

    Re: VBA to compare data in 2 sheets, issues with existing code

    thanks so much AlphaFrog, really appreciate the extra effort learning more and more each day

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Code to compare data in 2 sheets and delete duplicates
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-13-2013, 03:24 PM
  2. [SOLVED] Password issues with code that protects all sheets at once
    By Paragoomba in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2013, 12:57 PM
  3. [SOLVED] open a csv and compare the data with my existing worksheet.
    By sheffieldlad in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-09-2013, 07:19 AM
  4. COUNTIF Issues count blank cells among existing data
    By chrispulliam in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-21-2010, 01:52 AM
  5. Replies: 1
    Last Post: 08-15-2009, 05:52 AM

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