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

x
?
Solved

VBA to perform a search from one file to another.

Posted on 2014-01-20
3
Medium Priority
?
262 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 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

834 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