Macro to Compare Columns and Copy Paste Data

[Jiptastic]

Any idea why this isn't doing anything? It doesn't error, it just doesn't work.

It is supposed to compare Column A to Column B. IF the cell value in column A is greater than that in Column B, then it should paste Column A into Column C for the same the row.

Please Login or Register  to view this content.

[benishiryo]

hi Jiptastic. it works if your cursor is in A1. that's because you used:

Please Login or Register  to view this content.

use this instead:

Please Login or Register  to view this content.

you can also shorten the whole thing further:

Please Login or Register  to view this content.

[coreytroy]

Are you working with numerical values?
If so try,

Please Login or Register  to view this content.

[AKK9]

It's because of this:

Please Login or Register  to view this content.

You need to select a non-blank cell before you run your code otherwise it doesn't do the Loop.


Try this code below, selecting cells and copying and pasting is slow and unnecessary.

Please Login or Register  to view this content.

[Jiptastic]

Thanks for the quick responses. Benishiryo that makes perfect sense to me and works perfectly as well. AKK9 your solution is badass, but makes much less sense to me since I don't understand the formula you use. It makes sense though to skip the copy/paste and go straight for it.

[AKK9]

Well I guess it makes sense to use something you understand so you can edit it/reuse it in future.

I've never been very good at explaining things, but this is what my code does.

Please Login or Register  to view this content.

Cells(Rows.CountLarge, 1).End(xlUp).Row
This is finding the last cell in Column A that is not blank and gets the row number. So if I had values in A1 to A10 the first line becomes:
For r = 1 To 10
This tells the For loop to start at one, and then repeat the loop adding 1 each time, then stop at 10.

IIf(Cells(r, 1).Value > Cells(r, 2).Value, Cells(r, 1).Value, "")
IIf(Cells(r, 1).Value > Cells(r, 2).Value, Cells(r, 1).Value, "")
IIf(Expresion, True, False)
This is basically like the Excel formula "=IF(....". So you put the expression you want to test first, then True is what it returns if the expression is True. (In our case we want to return the value in Column 1 of the Row we are checking. If False I have just returned an empty string.)

Hope this helps.

[Jiptastic]

Yeah I get it now. Thanks AKK9!!