Results 1 to 3 of 3

VBA Compare Two Columns and Copy Matching Data

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Compare Two Columns and Copy Matching Data

    Hi, I wonder whether somoene may be able to help me please.

    I'm using the code below to compare columns on two sheets (1 column for each) and where there is a match, copy data from the 'Source' to 'Destination' sheet.

    Sub IDEASSignals()
        
        Dim Dic As Object
        Dim Dn As Range
        Dim Rng As Range
    
        'This script adds the "Flexible Resource Signal".
        With Sheets("All Resources")
            'This is the column on the 'Source' sheet you are comparing to the 'Destination' sheet.
            Set Rng = .Range(.Range("C8"), .Range("C" & Rows.Count).End(xlUp))
        End With
        
        Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
        For Each Dn In Rng
            Set Dic(Dn.Value) = Dn
        Next
        
        With Sheets("IDEAS")
            'This the column on the 'Destination' sheet you are comparing to the 'Source' sheet.
            Set Rng = .Range(.Range("D8"), .Range("D" & Rows.Count).End(xlUp))
        End With
        
        For Each Dn In Rng
            If Dic.exists(Dn.Value) Then
                'The first offset is the 'Destination' sheet i.e. 11 columns from column D. The Second offset is the 'Source' sheet i.e. 4 columns from column C.
                Dn.Offset(, 11).Value = Dic.Item(Dn.Value).Offset(, 4).Value
            End If
            Next Dn
        End Sub
    The code works fine, but I'd like to amend this, but I'm a little unsure about how to proceed.

    The script currently compares column C on the 'Source' sheet with column D on the 'Destination' sheet and where a match is found copy column G from the 'Source' sheet and pastes this to column O on the 'Destination' sheet.

    I'd still like to copy and paste the same columns, but I'd like to compare two columns from each sheet, so from the 'Source' sheet compare columns C and E to columns D and J on the 'Destination' sheet.

    I just wondered whether someone could possibly look at this please and offer some guidance on how I may achieve this.

    Many thanks and kind regards
    Last edited by hobbiton73; 02-18-2014 at 05:55 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to Compare and Copy to New Sheet Multiple Matching Records
    By virgIris13 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2013, 03:00 PM
  2. Compare row and highlight matching data Macro
    By damdam in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-17-2013, 12:45 PM
  3. trying to compare two worksheets for matching data
    By dc50 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2012, 06:04 PM
  4. Replies: 6
    Last Post: 03-08-2011, 06:34 PM
  5. Compare 2 columns and highlight matching data
    By schueyisking in forum Excel General
    Replies: 3
    Last Post: 08-13-2009, 10:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1