+ Reply to Thread
Results 1 to 19 of 19

VBA to compare 2 sheets/workbooks

  1. #1
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Question VBA to compare 2 sheets/workbooks

    Hey everyone, thanks in advance for the help.

    So I have 2 workbooks, and 1 worksheet in each one. Workbook1 is used for calculations and has all kinds of formulas and things and works as it should. I want to use workbook2 to check the results of the formulas from workbook1. Workbook2 will simply be a list of all of the expected values for the formulas from workbook1. I need to write a simple macro that will simply go down the list in workbook2 and see if the numbers match the values that should be present in each respective cell in workbook1. All I need is a simple pass-fail: if they match, move on to the next one. If they dont, then stop the macro and spit out an error message.

    I'd imagine this could probably be solved with some sort of if-then and/or do-while loop?

    I've never written a macro on Excel before, so that's why I'm asking for help. Any advice would be very much appreciated. Thank you again, in advance.

    --BA Patterson.
    Last edited by bapatterson; 07-12-2010 at 01:33 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    Why VBA? A regular formula can compare the cells for you.

    In Workbook2, add a Sheet2. In cell A1 enter something like this:

    =IF(Sheet1!A1 = [Workbook1.xls]Sheet1!A1, "", ADDRESS(ROW(),COLUMN())

    Now copy that cell down and across as needed...the ADDRESSES of the problem cells will appear.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Lightbulb Re: VBA to compare 2 sheets/workbooks

    This could possibly be a good alternative if I can't accomplish what I'm trying to do. I appreciate you taking the time to reply.

    The reason I'm looking for a macro is because workbook1 actually has many sheets to it (I just need to copy and tweak the macro for each page), and is a heavily used file. I'm trying to put a test button on each sheet of the page that will run the "test macro" and ensure that that page is working properly after each person makes changes and before each usage.

    Like I was saying, your method is a very resonable alternative if I am unable to accomplish what I am hoping for. If any further clarification is necessary, please don't hesitate to ask.

    --Thanks again,
    ----BA Patterson.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    Check every cell? Or check a particular column? Any restriction here at all on the comparison range?

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    This will simply compare every cell that has a formula in it:
    Please Login or Register  to view this content.

    Edit the references at the top.

  6. #6
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Lightbulb Re: VBA to compare 2 sheets/workbooks

    It varies from sheet to sheet. Most only need 2 columns to be checked, while a slim few need many more than this. It varies from sheet to sheet. In all cases, there will be information above and below the cells that need to be checked.

    I have attached an example sheet from what Workbook1 would look like.
    In this example, I need to check F5 - G7 and D10 - E56 (with exception of the words).

    I hope this answered what you were asking me about.

    --Thanks again
    ----BA Patterson.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: VBA to compare 2 sheets/workbooks

    In what order would this macro check the cells? By column, row, or what?

    I'm asking so that I would know in what order I would need to put my expected values into workbook2.

    --Thank you,
    ----BA Patterson.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    Generic "process all cells in the range" goes row by row. Just play try it out and you'll spot the pattern.

    This technique will process all cells with formulas in them. All other cells are ignored.

  9. #9
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Red face Re: VBA to compare 2 sheets/workbooks

    Hey thanks, I appreciate it. I'll try it out and let you know how it goes.

  10. #10
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Exclamation Re: VBA to compare 2 sheets/workbooks

    Hmmmm. I couldn't quite get this to work. It DID compare the correct 2 sheets that I wanted. It DID stick to only cells with formulas. The problem lies with the actual comparison.

    It will not compare the values that the formula returns. The returned values from the formulas on workbook1 and the expected value that I typed into workbook2 are exactly the same number, but it comes up with an error. If I erase the formula and hand-type in the exact same number, the comparison works. What do you suggest?

    --Thanks again,
    ----BA Patterson.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    I think your numbers are not exactly the same. Excel is very precise, but we are not and our perceptions frequently let us down.

    Let's see some of these formulas... I'll bet you've got values that look like 1-2 decimal values or even whole numbers for the display, but the actual calculated result is not the same as is being shown.

  12. #12
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: VBA to compare 2 sheets/workbooks

    Even before you posted this response, I verified to make sure that this was not the case. I tried a number that had all zeroes in the decimal place. I then expanded out the number upwards of 20 decimal places in order to ensure that the numbers were exact.

    I'll keep poking around with it and see if I can get it to take.

    --Thank you,
    ----BA Patterson.

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    Post a sample workbook so we can look at the problem together.

  14. #14
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: VBA to compare 2 sheets/workbooks

    Here you go. I put a sheet from WB1 and a sheet from WB2 together for this example workbook. The code has been changed accordingly. Please let me know if you have any problems opening/reading/understanding anything.

    --Thank you,
    ----BA Patterson.
    Attached Files Attached Files

  15. #15
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    This is only one workbook. The macro is designed to compare cells in two workbooks, the workbook the macro is in and another.

    According to your edits in the macro, you set WB1 = to ExampleSheet.xls, but that is the name of the macro'd workbook, which is supposed to be WB2.

    WB1 and ws1 need to be one workbook.
    WB2 (ThisWorkbook) and ws2 need to be the workbook with the macro in it.

  16. #16
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: VBA to compare 2 sheets/workbooks

    I know, it's originally 2 workbooks but I combined them just for the example. Here, I'll separate them back out; though it does seem that it should work correctly if you have all the locations named appropriately. Either way, here they are again but separated.

    --BA Patterson.
    Attached Files Attached Files

  17. #17
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    Hehe, I was really hoping you were going to upload a sheet that didn't look like that. Your request was unclear and the macro given compares cells in each workbook at the same address. Just listing values in column A of the second workbook means you're in for a macro maintenance nightmare...you'd have to have a line of code for every formula in the first workbook stating specifically which cell in column A of the second workbook to compare it to.

    Even the *smallest* of changes in your layout of WB1 would require tons of editing in your macro.

    In your WB2, put the expected values in the cells at the same address for the comparisons to work properly. The layout in the updated version is giving me correct comparisons.

    Remember that hidden "spaces" will cause text comparisons to fail. E2 failed until I made them actually match.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    04-01-2010
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Thumbs up Re: VBA to compare 2 sheets/workbooks

    Sweet Moses! We may be on to something here!

    I simply moved from listing them just down the column to making them all match the respective addresses.... Mission success. Only a couple of extra lines I need to add on to this now and I think we may have lift-off!

    Thank you very much, you're the man!

    --BA Patterson.

  19. #19
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA to compare 2 sheets/workbooks

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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