Lookup, Find and Replace macro needed

[jomili]

I need a macro solution, and this one is taxing my brain, so I'm appealing to the experts. I've provided examples of two workbooks, "Example.xls" and "Lookup.xls". Ignore the first 29 columns in Example; they represent data unnecessary for this task.

What I'm trying to do is populate columns BL, BM, and BN ("Division", "Purchase/Contract Description", "Vendor/Employee Name") in the "Example" workbook. The other workbook ("Lookup.xls") has three columns; "Lookup#", "Div", and "Name", that I use to pull information from. From that, and from other sources, I fill in the data in Columns BL, BM, and BN. What I can't find results in an #N/A.

Once I'm done, there's cleanup that needs to done for my Travel expenses in L2009 Travel. That's where I'm stuck. There are two tasks I need to accomplish.

The First is: In the LBB_ACCT column, if the acct is "L2009", if the "Descr" field entry begins with "T1001", or says "Travel", "Travel Exp", or "Travel Expense", or says "Reimbursement", "Volunteer Mileage Reimbursement", "Volunteer Reimbursement", or "Volunteer Travel Exp" and the Ln Vendor ID begins with a "2", then the lines are all travel related. For all of these lines I'd like to use the last 9 numbers of the Ln Vendor ID to lookup the Div and Name from my Lookup sheet, and put "Travel Expense" in the "Purchase/Contract Description" field.

The Second is: Some (most) of the lookup numbers for L2009 may not be on my list of numbers. In that case I'd like to pull the name from the LN Vendor Name column, then scan up and down the LN Vendor Name column for the same name. If it's found, pull the Div from column BL for that one and paste to the line I'm working on, put "Travel Expense" in column BM. If it's not found, paste the name from the LN Vendor Name column into column BN, put "Travel Expense" in column BM, and put "#N/A" in column BL.

Whew! I know it's a lot to ask. I start thinking about ways to do it all and my mind goes numb, so any help you can provide is greatly appreciated.

Thanks,
John

[Charles]

jomili,

I have some of your request worked out. However I do not understand what you want in the second part of your request.
I have a Test button in the worksheet for testing.

The Second is: Some (most) of the lookup numbers for L2009 may not be on my list of numbers. In that case I'd like to pull the name from the LN Vendor Name column, then scan up and down the LN Vendor Name column for the same name. If it's found, pull the Div from column BL for that one and paste to the line I'm working on, put "Travel Expense" in column BM. If it's not found, paste the name from the LN Vendor Name column into column BN, put "Travel Expense" in column BM, and put "#N/A" in column BL.

You refer to Scan. Scan what the Vlookup workbook or the main workbook.
If you are referring to the main then you already know the vendors name. Could you shed more light on this.
I attached what I have so far. You will find a button for test.
I have the code reference the Vlookup workbook and the Main. In the Vlookup I used "Sheet1" as the sheet name.

[jomili]

Charles,

A few things:
1) even though I can POST a zipped file, the filters on our network won't allow me to download or open most zipped files. Yours is one of those. Can you unzip and post it?

2) Regarding step 2: Many of the L2009 lookup numbers aren't on the "Lookup" sheet. However, prior to this step I filled in a lot of information using other data sources. It's entirely possible that in those ealier steps I filled in the Division for this same person. So, what I'd like to do is, using either the LN Vendor Name column or the LN Vendor ID column, scan up and down for a match. If a match is found, pull the Division from that match, and paste it in the L2009 line in the "Division" column, paste the LN Vendor Name in the same row under "Vendor/Employee Name" , and put "Travel Related" in the "Purchase/Contract Description" column.

Let me know if it's still not clear and I'll take another run at it. Thanks so much for your help.

John

[Charles]

jomili,

Ok, if I understand this. If the Vendor is not in the "Vlookup" workbook you want to then look at the "Main" workbook and find that Vendor name which is in column "AE" and the Id is in column "AD'. Now if we are looking at "L1009" and the ID and Name is not in the Vlooup we already have the Info for it in the Main workbook all we would need to do is populate the required info to the specified columns.
Does this sound right.
If necessary I can email you the project. PM me if you wish too do this.

[jomili]

That's exacly right, except you said "L1009" when you meant "L2009". And, we might or might not have the info in the data above. If we don't, then we'd want to fill the Division with "#N/A", and put the Ln_Vendor Name in the "Vendor/Employee Name" column.

I'll PM you with my email address. Thanks loads!

[Charles]

Thanks will look for PM and send the project too you.

[jomili]

Charles,

I got your solution, and it works beautifully. I examined your code, and it gave me a solution to the next problem I was going to have. Thank you so much for your work on this. Do you have any problem with me posting your solution so someone else might benefit from it before I close this thread?

Thanks,
John

[Charles]

John,

It's you project and I have no problem with the posing of the solution.
If I can be of further help let me know.

[jomili]

I've attached the solution Charles sent to me, and I hope someone else can get use of it. It's a very elegant solution.

Thank you, Charles, for all your help!

[Charles]

Hi,

Thanks for the update.