Automate the Comparison of 2 excel workbooks

derrynek

New Member
Joined
Jul 26, 2019
Messages
1
Hello!! New user and beginner to VBA.


I'm trying to automate the comparison of 2 excel workbooks and have a message box in the masterfile pop out, telling me if theres a math between the total sum of items in "Books-Raw Listing" & "Raw Listing" workbooks.


Here's the tricky part, the transaction codes in both workbooks are different but have same values. Hence, there's a mapping table in the masterfile which helps me to see which matches to which between the 2 wbs. Also, I only want to sum specific values at the time, ie: Only want to sum "Pages" from wb Books-Raw Listing and "RED","SW-OUT" & "DIV" in wb Raw Listing.


I'm aware a simple SUMIF will do the trick, but im trying to automate this function wherby clicking on a command button will have a message box tell me if there's a match or not.


Here's the code i'm currently using but has not worked:
Sub Compare()
wb2 = Application.WorksheetFunction.SumIf(Range("[Books-RawListing.xlsx]Transaction Analysis!A2:A13"), "PAGES", Range("[Books-RawListing.xlsx]Transaction Analysis!H2:H13"))
wb1a = Application.WorksheetFunction.SumIf(Range("[Raw Listing.xlsx]Raw Listing!A2:A13"), "RED", Range("[Raw Listing.xlsx]Raw Listing!F2:F13"))
wb1b = Application.WorksheetFunction.SumIf(Range("[Raw Listing.xlsx]Raw Listing!A2:A13"), "SW-OUT", Range("[Raw Listing.xlsx]Raw Listing!F2:F13"))
wb1c = Application.WorksheetFunction.SumIf(Range("[Raw Listing.xlsx]Raw Listing!A2:A13"), "DIV", Range("[Raw Listing.xlsx]Raw Listing!F2:F13"))

If wb2 = wb1a + wb1b + wb1c Then
MsgBox "Match!"
Else
MsgBox "No Match"
End If
End Sub
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,099,005
Messages
5,465,959
Members
406,456
Latest member
jmishra91

This Week's Hot Topics

Top