VBA VLOOKUP - Need to convert multifunction WS formulas to VBA.

I have 2 WB's that are currently linked with the following VLOOKUP formulas.

=IF(E28>0,IFERROR(VLOOKUP(E28,'O:\Manufacture\Procurement Item Master\[ITEM MASTER.xls]DATABASE'!$A$1:$G$950,2,0),"Item Not in Item Master. Enter a Description"),"")

=IF(E28>0,IFERROR(VLOOKUP(E28,'O:\Manufacture\Procurement Item Master\[ITEM MASTER.xls]DATABASE'!$A$1:$G$950,3,0),"Enter the Manufacture"),"")

=IF(E28>0,IFERROR(VLOOKUP(E28,'O:\Manufacture\Procurement Item Master\[ITEM MASTER.xls]DATABASE'!$A$1:$G$950,4,0),""),"")

=IF(E28>0,IF(G28<>"Customer",IFERROR(VLOOKUP(E28,'O:\Manufacture\Procurement Item Master\[ITEM MASTER.xls]DATABASE'!$A$1:$G$950,5,0),"Add Cost"),0),0)

I have successfully written the following code to handle the VLOOKUP, from the second WB and populate all of the correct fields. However the IF statement that is commented out will only work when the lookup happens in the same workbook

Note I tested the code first to lookup in the same sheet, once I got that working I moved to do it from a different sheet in the same WB, once I got that to work I moved to looking up the data in the other workbook which is how the system currently works. I have attached samples of the files for reference.

Please Login or Register  to view this content.

The problems I am having are:
1. I need to check if the Part number field is empty, if so I need to skip to the next part number. Currently the VLOOKUP returns #N/A if the Part number is blank or not found. I tried IF cl = "" Then goto Next cl but could not figure out how to get it to work

2. If a the value = #N/A after a lookup, I would like it to display nicer text such as the formulas above. i.e please enter the description, manufacture, cost, etc. This only works when I use the code above on the same workbook. I Don't UNDERSTAND WHY. Still a NOOB.

3. I need to check if the description, supplier, manufacture and cost fields contain data, if they do I don't want to overwrite it. The user may end up running the check after they have entered data into the fields manually. If new parts get added to the Part number list, they should be able to run the check only on the part numbers that do not have the adjacent fields completed already.

3. I need to be able to look at the Provided by column and see if it says "Customer", if it does I need the Cost column to display a zero dollar amount

Any help will be greatly appreciated.

Ok, I have found that I can use the following code to only lookup the cells that contain values and I no longer get a bunch of #N/A values. But I still cant change the cell value if the cell does equal #N/A. Would love some help here

Please Login or Register  to view this content.

I was able to get the following code to perform the lookup on the Part Number, enter text in the description cell if the lookup does not find a part, and not overwrite any manually added description. This will work for the Description, and Manufacturer fields but the supplier and cost fields will need to be updated.

Please Login or Register  to view this content.

Any ideas out there?

Solus,
Here is the current code.

Please Login or Register  to view this content.

With the way it is now, if I have manually entered data for a part that does not exist in the Item Master and I run the code, it will overwrite it and tell me to enter the information again. I need to be able to check if the cell is empty, if it is then I want to tell the user to add the description, manufacture, and cost if it is not then I want it to leave it alone. (See VLOOKUP Formulas Above). I will attach an updated WB in a few minutes for you as well if that will be easier.

Solus,
By the way I wanted to let you know that I realize I can shorten this up by doing the following, but until I had it all working it was easier to work on one section at a time.

Please Login or Register  to view this content.

Ok, I have tried to modify this in several ways to work how I need it to with no luck.

I thought that if the lookup returned an error, and the destination cell for the lookup value was empty that it would return the value "Add Description" like below, but it does not work, it puts the #N/A

Please Login or Register  to view this content.

for the Cost field lookup I tried to do an offset to the Customer column and if the value was "Customer" put a 0 in the cost cell. I cant remember how I wrote it, so I cant put it in here right now, but all it did was put a 0 in every cost cell down the sheet starting at the cell that had the "Customer" value.

Attached is a workbook with complete code that is listed above (Both Long and Short versions).

Solus, I have updated the shorthand code to include an offset for looking for a value of "Customer" in the Provided by column and it is working.

The only thing that I need to be able to do now is not overwrite any cells that get information manually entered if the part number is not found in the database. So it needs to check for an error on lookup, if there is an error (Part Number Not Found) and the destination cell is empty I need it to put the V1, V2, etc. value in the cell if the destination cell is not empty I want it to leave it as is.

Here is my most recent code.

Please Login or Register  to view this content.

After giving it some thought, I realize that line of code I mentioned 2 posts back (see #1 Below) was in fact working correctly. It would enter the text if the error was found and the destination cell was empty on the first run. It would then error out #N/A after you ran it with a value in the cell. The reason for this, is that a vlookup will always return that error unless you tell it not too. I was only telling it not to give the error if the cell was empty and there was an error. I need to tell it what to do if the cell is not empty. ????? I thought I could make a new variable for the value of the description cell and then add another line of code see #2 below and it worked but it put the same value in every cell that was not blank. I need to be able to check each cell and adjust the value of x accordingly. What a pain VLOOKUP is in VBA. LOL

#1

Please Login or Register  to view this content.

#2

Please Login or Register  to view this content.

I think I got it. Thanks for giving me a place to gather my thoughts. I would still like to get some input just in case there is something I could do better with this code. I will post the entire thing after I test all 4 lookups. Instead of adding another variable I just changed this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Here is the working code. I will leave this thread open for a day or 2 just to see if anyone has input on how to improve this. I am learning, and unfortunately only spend a lot of time with VBA when an issue comes up. At which point I spend a week on something as seen here. I am sure someone out there would have nailed this in a few hours as opposed to my 30 or so.

Please Login or Register  to view this content.