PDA

View Full Version : VBA Auditting Macro



ddiag001
01-14-2008, 08:54 AM
Hello,

I manage some product pricing quotation tools based on excel for business software company. When there are changes in pricing, a new quotation tool (QT) is released from the global HQ and then I'm responsible for localizing these the Latin American Regional QTs from the global QT.

I want to set up a macro which compares specific values in specific cells in two different tools (excel workbooks) so that if the values do not equal, it will display a " Failed" message in a specific cell on the new QT.

The purpose of this is to compare the two QTs and to test the differences between the new released QT and the current QT which is going to be replaced.

I want to be able to use this macro everytime I have a new QT so that I can compare these values. Is there a way to set up this macro so that it's not could be applicable to different workbooks?

In addition, after the test, I want to be able to remove this macro or any coding from the QT.

Thanks for the help.

grichey
01-14-2008, 10:36 AM
you want to compare every cell on the entire workbook? Just set up a third work book identical in structure to the other 2 QTs and put the same formula in every cell you want to compare. Just reference the 3rd work book to see what cells have changed.

if('[workbook1.xls]sheet1!A1'<>'[workbook2.xls]sheet1!A1',"Fail","unchanged")

ddiag001
01-14-2008, 10:47 AM
Thank you for your reply, I currently test in the way you have suggested. However, I would like to explore the possibility of setting up a more automative way of testing, so that I do not need to go into the formulas and re- reference to different workbooks.

For example, a macro that when I get a new tool I can just install into the tool, (the Macro will automatically compare the new workbook that it is installed into and the last workbook that it was run on), run and in the cell to the right of each cell I want to test, a " Pass" or "Fail" text would appear.

grichey
01-14-2008, 11:05 AM
i still think the way you're doing it now will be the most effective. Rereferencing should be 3 click type process just using replace all [workbook1.xls] with [newworkbook1.xls]. If you're going to have it 'remember' the info on the last file, you're going to get a much much larger file....