Solved

VBA to perform a search from one file to another.

Posted on 2014-01-20
3
248 Views
Last Modified: 2014-01-27
I need to search a file that has over 200,000 rows. If the specific reference in my file is on the other file I need the macro to put Yes in my file and if its not there to put No.

So..... the file is in the path G:\Clients\Open Invoice\Open Invoice - UPDATED.xlsx

My file [the one with the Macro] needs to look up the Reference in C5 [on tab called PD3] and see if this is on the file ‘Open Invoice - UPDATED.xlsx’. If the Ref is found enter 'Yes' in my file [on tab called PD3] cell J6 and if not found put 'No'
0
Comment
Question by:Jagwarman
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39794523
You don't need a macro just a vlookup:-

in D5 (the column you want yes or no in) put:
=If(iserror(VLOOKUP($C5,'[G:\Clients\Open Invoice\Open Invoice - UPDATED.xlsx]Sheet1'!$A:$A,1,0)),"No","Yes")

- you may need to change the 'sheet1' to refer to specific sheet and the range $A:$A to refer to the appropriate column

Copy the formula down all of the rows in your target spreadsheet

Reg
0
 

Author Comment

by:Jagwarman
ID: 39802336
regmigrant bizzarley I could not get this to work but, I changed iserror to ISNA and it does work. Fingers crossed that it will always return the correct vallue.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39802460
iserror should work (for excel 2003 and up) but hopefully you will be ok with ISNA - good luck
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

679 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