Compare 2 Sheets of Data (Macro)

Question

I am trying to get a macro that takes 2 sheets from a Workbook and compare 2 different columns of data.

Find a number in 'Price Variances'!D2:D999999 and try to match it to 'Finance All'!E2:E999999
If they match, then take the corresponding data from 'Finance All'!G2:G999999 and paste it into the corresponding rows in 'Price Variances'!U2:U999999.

Clarification

I want to look at a value in a cell in 'Price Variances', Column 'D', Row '2', then see if there is a match in 'Finance All' column 'E' (Look through entire column for a match).

If there is, I want to paste data from the corresponding row of the match from 'Finance All', Column 'G' into 'Price Variances', Column 'U', Row '2' (This is the same row of the original cell that we were searching for a match from).

This would need to process for every row there after in 'Price Variances', Column 'D'.

Below is what I have so far.

Sub Price_Variation_Finance_Match()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("'Finance All'!E2:E999999")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Daily Pricing (5)"). _      
    '   Worksheets("Price Variances", "Finance All").Range("E2:E999999")
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 17) = x
        Next y
    Next x

End Sub

I believe my problem lies within the last 'x' in the If x = y Then x.Offset(0, 17) = x

Below is the Original Macro

Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("C1:C5")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Book2"). _
    '   Worksheets("Sheet2").Range("C1:C5")
    '
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub

what problem are you having? what's the macro right now? You won't get any help on this site if you ask people to "just fix it", but can get some useful help in trying to spot a problem or issue. — Maciej
My problem is that what I have is not working, and I cannot seem to find a fix for it. And the macro right now is posted above. I have come to the end of my rope in terms of getting it to work - The macro runs, but no data is moved over. — Bcarr91
what is the value of x on that line that you think is the cause of the problem? isn't it being replaced by the same value as was originally in the cell? Have you tried setting x.Offset(0,17) to some static value? Is column U being populated with anything? What does Selection contain? — Maciej
Yes, it is being replaced by the same value, but I need it to be replaced by data that is 2 rows to the right of that cell. The 'x.Offset' should be telling it where to put the data (Column U), and the selection should be 'Price Variances', Column 'D'. — Bcarr91

APrough APrough · Accepted Answer · 2014-09-22T15:35:49

Your If statement is going to return the original value of x. I think instead, you would want

If x = y Then x.Offset(0, 17) = y.Offset(0, 2)

This gives you the value found in the y column two columns to the right of the lookup.

Note that this macro is VERY slow, since it is cycling through each cell in y, even if it already found a match. If you want the first one found, then I suggest chaning your For Loop to

For Each x In Selection
     For Each y In CompareRange
         If x = y Then
            x.Offset(0, 17) = y.Offset(0, 2)
            Exit For
        End If
     Next y
 Next x

Or better yet, just use VLOOKUP, which will do this whole function for you nicely.

Compare 2 Sheets of Data (Macro)

Clarification

1 Answers