CODEWITHSUNDEEP
vbaexcel
Bcarr91
Bcarr91

Reputation: 25

Compare 2 Sheets of Data (Macro)

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

  1. Find a number in 'Price Variances'!D2:D999999 and try to match it to 'Finance All'!E2:E999999

  2. 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

Upvotes: 0

Views: 801

Answers (1)

APrough
APrough

Reputation: 2701

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.

Upvotes: 1

Related Questions