Insert or copy formulas using VBA

I think Trebor may have one too many right parentheses in the formula ')'.

Try:

Please Login or Register  to view this content.

In addition to make the formula look cleaner you may want to declare your lookup table as a 'Named Range'. This can be done manually (Excel 2003):
Insert > Name > Define or in newer versions of Excel see http://www.dummies.com/how-to/conten...xcel-2007.html

It can also be done in VBA such as (the named range is any name of your choice):

Please Login or Register  to view this content.

Lewis

Hi Robert and Trevor,
Thanks for the code fix! It works perfectly with only one parentheses in the formula. I have also used the range declarations you suggested and it certainly cleans up the code. However, after running it didn't return the correct VLOOKUP result and I realized that VLOOKUP needs to have the 1st column in target range sorted a to Z, or numerically. I will have to add a sort function for the "Master Store List" Range that runs prior to the Vlookup... unless one of you know of another VBA call function that looks for a specific value without having the target range sorted in any particular manner?
Thanks again!
Cesar

Try

Please Login or Register  to view this content.

4th parameter as 'False', indicates you are looking for an EXACT MATCH and the range DOES NOT have to be sorted. See http://office.microsoft.com/en-us/ex...005209335.aspx

Lewis

Lewis and Trebor,
I really appreciate the help on the coding. I've made your suggested changes and they work great! Funny how I've used Vlookup for years as a worksheet function and never entertained the "false" argument-- see how old dogs can learn new tricks.
Take care!
Cesar