[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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
Medium Priority
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
  • 2
LVL 19

Accepted Solution

Arno Koster earned 2000 total points
ID: 35446536
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

by:Arno Koster
Arno Koster earned 2000 total points
ID: 35446543
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

ID: 35447326
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

872 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