Results 1 to 2 of 2
  1. #1
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts

    Generate custom code to find matched cells between any two ranges on same or different sheets

    There have several requests for code that will compare two ranges from either the same sheet or between sheets for matches. I have put together a GUI using a UserForm that will allow the user to customize any search ranges, view the matched cells in highlight, and/or generate a module with the code for the ranges selected. There is no need to know any VBA. The user only needs to create a form button and assign the newly created routine to it.

    When the user clicks the "Run Code" button, a userform will open. Range 1 and range 2 are selected by clicking on the underscore buttons then dragging a range similar to selecting the range for a chart series. The range will populate the boxes as you drag.

    Open the form then select both ranges by clicking on the control and dragging the range
    Compare1.png

    Ranges from different sheets are selected
    Compare2.png

    If you click "Start Compare", the code will find all matching cells between both ranges and highlight them.
    Compare3.png

    If you click "Generate Code", a custom code for your ranges will be created inside a newly created standard module.
    Compare4.png

    Just place a form button on your sheet and assign the macro to it.

    Export the form from the sample workbook:
    Alt-F11 to open VB editor > Select the form "CompareSheets" in the VB Project window > File > Export File... > Navigate to save destination > Save

    Import to any workbook:
    Alt-F11 to open VB editor > File > Import File... > Navigate to file > Open.

    I hope this helps at least one person out.

    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2016-03-12 at 23:42.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts

    Testing between workbooks

    Continued some testing. The code is universal and will find duplicates for two ranges on the same sheet, different sheets, and even between workbooks. The generated code is universal as well.

    Maud

Tags for this Thread

Posting Permissions

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