[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

VlookUp Function in Excel VBA

Within my workbook for all my worksheets whose name starts with "Series", I have a the same VLOOKUP formula in cell B2.  What I would like to do is to create the formula in a VBA function, so if the range changes for any reason, I can just modify the formula in the VBA function module as opposed to having to modify it on each each.

I'm just not sure how to go about it.  I know segments of code "Application.WorksheetFunction.VLookup" and "Application.Volatile True" hold the key to making this happen.  Here's a link to another forum that discusses this topic (http://www.ozgrid.com/forum/showthread.php?t=52372) and also this one (http://www.mrexcel.com/forum/showthread.php?t=59347), but I'm still not able to make heads or tails of how I apply it to my situation.

If you look at my attached worksheet and click on each "Series" worksheet, you'll see my VLOOPUP formula in cell B2.  So instead of the VLOOKUP formula being there, it would be the function.  Something like =VLKUPttls().  I'm just not sure how to make it happen.

Do any of you experts have any ideas?
SampleVlookup.xls
0
KP_SoCal
Asked:
KP_SoCal
  • 3
  • 3
  • 2
3 Solutions
 
Patrick MatthewsCommented:
With respect, this is a very bad idea.  If you are concerned that the range reference to Main!A2:B5 might change, then do something like:


1) Rewrite the formula as =VLOOKUP(A2,Main!A:B,2,FALSE).  That is perfectly good if you may be adding rows on Main

2) Create a named range, lookup_table, referring to =Main!$A$2:$B$5.  If the range changes, all you need to do is modify the Name.  The formula then becomes =VLOOKUP(A2,lookup_table,2,FALSE)

3) Make the Name itself dynamic by making lookup_tablae refer to:

=Main!$A$1:INDEX(Main!$1:$65536,MATCH("ZZZZZZZZZ",Main!$A:$A),MATCH("ZZZZZZZZZ",Main!$1:$1))
0
 
KP_SoCalAuthor Commented:
Step 1 won't work for me because my real worksheet has different tables of data below the one I need my vlookup to focus on.  

Step 2 should be exactly what I need though, great!  

I'm not sure what you mean by step 3 in making it dynamic? Also, what's "ZZZZZZZZ". Sorry, this is probably a dumb question, but I would really appreciate it if you could clarify. Thanks so much!!!  =)

KP
 
0
 
Curt LindstromCommented:
Regarding dynamic data, I use the following formula with a defined name.
=OFFSET(Main!$A$1,0,0,COUNTA(Main!$A:$A),COUNTA(Main!$1:$1))

I added this defined name to your workbook. Now you can increase the size of you data in sheet "Main" without changing you formulas. Adding rows below or adding columns to the right.



Regards,
Curt
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Curt LindstromCommented:
Here is you file with the defined name added.

Regards,
Curt
SampleVlookup-1.xls
0
 
Patrick MatthewsCommented:
KP,

Be happy to :)

Please read this brief introduction from Excel MVP Deb Dalgleish: http://www.contextures.com/xlNames01.html#Dynamic

The idea is to use a formula to determine the range, and not fix the range using a static reference.  (=Main!$A$2:$B$5 would be a static reference, as unless you actually use the Insert or Delete operations, that reference will not resize on its own.)

So, my suggestion of making the Name refer to

    =Main!$A$1:INDEX(Main!$1:$65536,MATCH("ZZZZZZZZZ",Main!$A:$A),MATCH("ZZZZZZZZZ",Main!$1:$1))

does the following:

1) Range starts at Main!A1

2) The INDEX expression attempts to find the "last cell" used on worksheet Main, and uses that cell as the end point of the range reference

3) It is assumed that we can determine the "last row" used by finding the last cell in Column A with a value, and the "last column" by finding the last cell in Row 1 with a value

4) That "ZZZZZZZZZ" is appropriate if the column/row contains text; if the column/row contains numbers instead, use a really large number like 10^200:

    =Main!$A$1:INDEX(Main!$1:$65536,MATCH(10^200,Main!$A:$A),MATCH("ZZZZZZZZZ",Main!$1:$1))

Curt's OFFSET suggestion will work, and I used to do it that way, but nowadays I recommend avoiding OFFSET because it is a volatile function.  I also have moved off of using COUNTA to determine "last column/row" because if there are empty cells dispersed within Column A or Row 1 it can throw off the result.

Patrick
0
 
Patrick MatthewsCommented:
Note also that the dynamic expression for Excel 2007 and later would have to be:


    =Main!$A$1:INDEX(Main!$1:$1048576,MATCH("ZZZZZZZZZ",Main!$A:$A),MATCH("ZZZZZZZZZ",Main!$1:$1))

    =Main!$A$1:INDEX(Main!$1:$1048576,MATCH(10^200,Main!$A:$A),MATCH("ZZZZZZZZZ",Main!$1:$1))


Row inflation :)
0
 
KP_SoCalAuthor Commented:
Thanks guys so much for the feedback.  I have what I need now to make it work! =)
0
 
KP_SoCalAuthor Commented:
Hey Patrick, if you're still out there, I just posted a question that relates to your suggestion.  I really like you approach, but my new scenario is a little more complex.  If you have any ideas on how to make it work, it would be much appreciated.  The link to the new thread is below.  Thanks!

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25556129.html

KP
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now