how do i write in excel script that when pressing on macro button it will check match with other columns file?

Posted on 2011-04-21
Last Modified: 2012-05-11
i have two files and i would like to run macro that will check if data in columns C and E in second file match to same columns in first file, if match  copy column F from second file to column G in first file and do this for all rows in second file
if not find match add this row to first file (add row with column C, E and G)  

please advice
Question by:gvilbis
    LVL 19

    Accepted Solution

    place this code on the Sheet1 section of the VBA editor of the first file.
    update the filename to match your situation
    run the process macro.

    Sub process()
    Dim src As Worksheet
    Dim dst As Worksheet
    Dim item As Range
    Dim posC As String
    Dim posE As String
    Dim valid As Boolean
        Const filename As String = "[filename].xls[x]"
        '-- first file (this file) as destination
        Set dst = Me
        '-- second file as source
        Set src = Workbooks.Open(filename).Worksheets(1)
        '-- verify columns C & E
        valid = True
        For Each item In src.UsedRange.Rows
            posC = item.Cells(3).Address
            posE = item.Cells(5).Address
            If src.Range(posC) <> dst.Range(posC) Then valid = False
            If src.Range(posE) <> dst.Range(posE) Then valid = False
            If valid = False Then Exit For
        Next item
        '-- copy only if verified columns match
        If valid Then
            src.UsedRange.Columns(6).Copy dst.Range("G1")
        End If
        '-- clean up
        src.Parent.Close False
        Set src = Nothing
        Set dst = Nothing
    End Sub

    Open in new window

    LVL 19

    Assisted Solution

    I am now starting to doubt if I have taken the right path, can you clarify ?

    is the cell copying done on a row-to row basis (eg. row 1 matches, copy F1 to G1. row 2 does not match, copy C2 E2 G2 to new (unused) row) ?
    or do you want to copy all used F cells to G when C and E column comlpetely match ?

    Author Comment

    i would like to copy cell F to G if C and E are completly match, if not find match at all rows in the file then add this line (C E and F ) at end

    in your code:
    If src.Range(posC) <> dst.Range(posC) Then valid = False
    If src.Range(posE) <> dst.Range(posE) Then valid = False
    If valid = False Then Exit For

    is it not should be something like:

    If src.Range(posC) == dst.Range(posC) Then valid = True      
    If src.Range(posE) == dst.Range(posE) Then valid = True
    If valid = True Then Exit For

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now