• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 85
  • Last Modified:

VBA :Match columns from from workbook A & B, return another column from workbook A to workbook B

I have 2 workbooks A & B.The workbooks are already defined.

I have an issue with part of my code where I am trying to compare column A from workbook A and Column D from Workbook B. If it matches then I want to copy corresponding column B to Workbook column E.

I started to write the following code but not sure how to proceed further in comparing the columns and copying as I am fairly new to VBA. I am not trying to match row by row, I am checking the entire column.


Dim strRangeToCheckA As Range
Dim strRangeToCheckB As Range
Dim strRangeToCheckC As Variant

Set strRangeToCheckA = RSC.Sheets("Balance").Range("A:G")
Set strRangeToCheckB = Brostacc.Sheets("Stock").Range("A:U")
Set strRangeToCheckC = Brostacc.Sheets("Stock").Range("U:U")

If Application.VLookup(LookFor, strRangeToCheckA, 2, False) = Application.VLookup(LookFor, strRangeToCheckB, 1, False) Then
  strRangeToCheckC = Application.VLookup(LookFor, strRangeToCheckA, 7, False)
End If

I appreciate the help and Thanks in Advance!
0
ABINAYA MOHAN
Asked:
ABINAYA MOHAN
Advertise Here
  • 8
  • 6
1 Solution
 
NorieVBA ExpertCommented:
So you want to go through column A on the sheet 'Balance' in one workbook and find matched in column D on the sheet 'Stock' in the other workbook?

If a match is found you wan to return the value from column B on sheet 'Stock' and put it in column E on sheet 'Balance'?

Is that correct?
0
 
ABINAYA MOHANAuthor Commented:
Yes,that is my Scenario.
0
 
Martin LissOlder than dirtCommented:
Can you supply sample workbooks?
0
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.

Sign Up
 
ABINAYA MOHANAuthor Commented:
Attached is the Sample Balance sheet in which I have added comments in it as in which column has to be compared and which column has to be copied and also Sample Stock sheet.
Sample-Balance-Sheet.xlsx
Sample-Stock-Sheet.xlsx
0
 
Martin LissOlder than dirtCommented:
You say
trying to compare column A from workbook A and Column D from Workbook B.
Column "A" on your supplied Sample-Balance-Sheet is labeled "DealerNum" an Column "D" on your supplied Sample-Stock-Sheet is labeled "Tel nbr" which seems incorrect. I assume you meant
trying to compare column A from workbook A and Column B from Workbook B.
0
 
Martin LissOlder than dirtCommented:
And once a match is found, is it the telephone number you want to copy?
0
 
ABINAYA MOHANAuthor Commented:
Yes Sorry for confusion,The comments in the Workbook is the correct one.
0
 
Martin LissOlder than dirtCommented:
I guess I should have read them <embarrassed>
0
 
Martin LissOlder than dirtCommented:
Which workbook should contain the code?
0
 
ABINAYA MOHANAuthor Commented:
Its a different workbook entirely different from these 2 files.I have defined the main code file as "SourceFile"
0
 
Martin LissOlder than dirtCommented:
Try this. There are a couple of places where you'll need to change the paths to your workbooks.
29069303.xlsm
0
 
ABINAYA MOHANAuthor Commented:
Thank you !
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
 
ABINAYA MOHANAuthor Commented:
I am trying to do the same kind of find and copy with another new file. But when I do that the performance of the vba goes down.It takes almost 25 minutes to run the entire program. Is there any particular reason that can happen? When I try to do any kind of operation with the new file the performance goes down.
0
 
Martin LissOlder than dirtCommented:
I am trying to do the same kind of find and copy with another new file. But when I do that the performance of the vba goes down.It takes almost 25 minutes to run the entire program. Is there any particular reason that can happen? When I try to do any kind of operation with the new file the performance goes down.
You should open a new question for that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Start This Course
  • 8
  • 6
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now