VBA to perform a search from one file to another.

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'
JagwarmanAsked:
Who is Participating?
 
regmigrantConnect With a Mentor Commented:
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
 
JagwarmanAuthor Commented:
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
 
regmigrantCommented:
iserror should work (for excel 2003 and up) but hopefully you will be ok with ISNA - good luck
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.

All Courses

From novice to tech pro — start learning today.