Excel 2007 : Creating a Compare Macro

[devildog53]

I'm trying to create a macro that will allow the user to compare any two columns in a spreadsheet. So, they would click on the macro to run and a pop up would come up asking which two columns they would like to compare. I've searched around and haven't found another example of this. I know it can be done in Access, but can it be done in Excel too? My leader at work seems to think this would be easier than going through the function menu in order to accomplish this.

Thanks!

[JBeaucaire]

My leader at work seems to think this would be easier than going through the function menu in order to accomplish this.

Ask your leader why they think this, what do they have in mind?

What is the expected results of your macro?

[MarvinP]

Hi devildog53 and welcome to the forum,

Creating VBA code to ask which two columns are to be compared increased the complexity of the problem a lot. Find an example that compares two columns and returns True or False depending on if all the row values match exactly. After looking at the answer, change a value in one of the columns to ensure it works. Then read about Array Formulas and see how complex this could become.

http://www.cpearson.com/excel/ArrayFormulas.aspx
http://www.ozgrid.com/Excel/arrays.htm

[devildog53]

Thank you both for your help.

JBeaucaire,

They just think it would be easier to have a popup ask which columns to compare as opposed to going through the function menu. A lot of the people on my team do not possess a great amount of Excel skills. The expected results of my macro is to have a popup come up asking which rows to compare, after inputting the rows the macro will then use the exact statement to compare the two columns one row at a time (probably safest in the final column). So, my final output would have the exact statement in every cell in column XFD. So in XFD1, it's comparing the data in A1 to B1 and returning a True/False statement. In XFD2, it's comparing A2 and B2, and so on. Every cell in XFD would be comparing its comparable cells in the same row. Sorry if this isn't too clear.

Marvin,

Thank you for your input. We have discussed the possibility of the user actually editting the vba code that the macro creates, but I was wondering if there's any way that an input can be created to do this instead.

[devildog53]

Here's what I have so far. I just don't know the syntax to substitute my entered values into the string highlighted in red.

Please Login or Register  to view this content.

[JBeaucaire]

As per forum rules, you need to edit that post above and add CODE tags around the code like I'll show here (demonstrated in my signature as well).

Try this:

Please Login or Register  to view this content.

[devildog53]

Sorry for the delayed response. I got busy with the holidays.

I tried to run your last code and received the "cannot compare a column with itself" error every time I tried to run it. So, I'm not sure if it works or not.

I've also added some formatting into the coding that I've done. Here's my latest code. If I could only substitute the fields I input into the exact statement then I'd be set. But I don't know how to do that.

Please Login or Register  to view this content.