Macro to Compare Columns and Copy Paste Data

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

Do while activecell.value <> empty

use this instead:

Do While Cells(Y, 1).Value <> Empty

you can also shorten the whole thing further:

Sub Compare_Columns()

Dim Y As Integer
Y = 1

Do While Cells(Y, 1) <> ""
    If Cells(Y, 1) > Cells(Y, 2) Then
        Cells(Y, 3) = Cells(Y, 1)
    End If
    Y = Y + 1
Loop

End Sub

It's because of this:

Do while activecell.value <> empty

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.

Sub compareColumns()
    For r = 1 To Cells(Rows.CountLarge, 1).End(xlUp).Row
        Cells(r, 3) = IIf(Cells(r, 1).Value > Cells(r, 2).Value, Cells(r, 1).Value, "")
    Next
End Sub

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.

Sub compareColumns()
    For r = 1 To Cells(Rows.CountLarge, 1).End(xlUp).Row
        Cells(r, 3) = IIf(Cells(r, 1).Value > Cells(r, 2).Value, Cells(r, 1).Value, "")
    Next
End Sub

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.