[SOLVED] Use VBA to use Vlookup across two workbooks

[Wraithxiv]

I have two separate workbooks, the first has a list of names starting if F2. The second workbook has a list of names starting in A1 to perform the vlookup. What I would like to do is use VBA to create a macro to lookup the names in workbook1 F2 in workbook2 A1 and if a match is found to return "CUS" the working vlookup I used in the sheet looks like:

The sheet name I used this formula in is call "test".

Even though this isn't VBA code, I thought I would put it in tags just in case.

Please Login or Register  to view this content.

If we can convert this to work in VBA that would be great. I have tried putting

Application.WorksheetFunction. in front of it.

Thanks in advance!

[arlu1201]

If you need the formula in B1 for example just say -

Please Login or Register  to view this content.

Perform a manual vlookup and record the code. Take that code and put it in after the "=" above.

[Wraithxiv]

Thank you for the fast response. It was very helpful.

I ended up with

Please Login or Register  to view this content.

Although I get #NA instead of "" like I want when a match can't be found. This also happens when just using the formula in the sheets.

Do you know of a way to avoid getting #NA and have it leave a blank as desired?

Thanks again!

[arlu1201]

If you had to put your formula in excel, it would be -

Please Login or Register  to view this content.

Record the code using the above and it will accordingly give you the syntax.

[Wraithxiv]

Thanks again for that quick response. That works quite well.

I got too caught up in this working that I had forgotten that I am trying to pull the sheet I want to use the vlookup on with a combo box.

Is there a way to replace [Book1] with a variable.
It doesn't seem like there would be since this is copying the formula into the cell to be executed instead of actually performing it in the VBA script.

I might ave to go back to my original play and do this with VBA.

Maybe using "find" but I'm not quite sure how to use it yet.

Any more suggestions would be appreciated.

[arlu1201]

You can replace Book1 with a variable but you have to ensure the syntax is intact. Try something in the lines of

Please Login or Register  to view this content.

[Wraithxiv]

From the recorded macro:

Please Login or Register  to view this content.

I also show how I declared my variables.

CompareFile is the value returned from my combo box.

With the above code I get an error "Object doesn't support this property method"

Did I not write my syntax correctly or is there an issue with my variable?

Thanks again for all the help!

[arlu1201]

Is CompareFile declared anywhere? How does the code know which is the file you are talking about?

[Wraithxiv]

I have "Public CopyFile As String" at the top of my Module before my Sub(s).

If it will help I can try and post all of my code from the use form and my module.

[arlu1201]

Have you assigned your workbook to the variable CompareFile? How will the code know that "CompareFile" means the file that you are talking about?

[royUK]

VLOOKUP works across workbooks without VB

[Wraithxiv]

I apologize, In my previous post I meant "Public CompareFile as String" and not CopyFile.

So here is my userform:

Please Login or Register  to view this content.

And this is my module:

Please Login or Register  to view this content.

All of these have used in several other Sub(s) and I didn't want to have to keep re-typing them.

Please Login or Register  to view this content.

The sub finds a column header and selects the used rows. I am trying to determine matches based on the values of these cells using the vloopup and then put in "CUS" in the adjacent cells.

Thanks again for you help, I hope this is more helpful.


P.S. If I get rid of the vlookup and just have:

Please Login or Register  to view this content.

and the workbook I am trying to reference in the vloopup becomes active instead of the destsheet. To me this tells me the variable is properly assigned.

[Wraithxiv]

I figured this out. Thank you for all of your help.

My issue was I had assigned the name of the sheet I was referencing from my combo box two different ways. The first was Public "CompareFile as String" and the second was

as a workbook when I set "wkbcompare" I was using the wrong variable in my vlookup.

Thanks again for all of you time and effort helping me.

If you could help me figure out how to set this thread as SOLVED I would appreciate it.

Thanks again.

[arlu1201]

Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

If you found any post helpful, please rate it accordingly.

[Wraithxiv]

Will Do, and thanks again!