VBA Compare Columns & Copy Data

  • Hi, I wonder whether someone could help me please.

    I'm using the code to compare two columns on two separate sheets, and where there is a match copy data from each row to the 'Destination' sheet.

    To be more precise:

    • Use column D on the "All Resources" (Source sheet) sheet to compare against column E on the the "All Data" (Destination) sheet then
    • Search and column G on the 'Source' sheet and of a value is found store this as the 'Dn' value, then
    • Search column M on the 'Destination' sheet and if a value is present store this value, then
    • If a match is found between both sheets paste the data from column H to column H on the 'Destination' sheet.

    The code works fine, but I'm having a little difficulty with making the change.

    What I'd like to do is amend this section of code:

    If Dic.exists(Dn & Dn.Offset(, 8)) Then
                'Where the values stored in the 'Dictionary' variable match, the values from column H are copied and paste into column H on the 'Destination' sheet.
                'The first offset is the 'Destination' sheet i.e. 3 columns from column E.
                'The middle offset is the value being checked in column M i.e. 8 columns from column E on the Destination' sheet.
                'The last offset is the 'Source' sheet i.e. 4 columns from column d.
                Dn.Offset(, 3).Value = Dic.Item(Dn & Dn.Offset(, 8)).Offset(, 4).Value
     End If

    so that instead of using Offset(,8)), the code searches a specific cell, in this case cell B3.

    I've tried making the following changes, and although the code runs, the value are not being paste into the 'Destination' sheet.

    If Dic exsits((Dn.Range("B3")) then

    Dn.Offset(, 3).Value = Dic.Item(Dn.Range("B3")).Offset(, 4).Value

    I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.

    Many thanks and kind regards


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!