1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

vlookup macro

Discussion in 'Business Applications' started by anglin_fool, Mar 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. anglin_fool

    anglin_fool Thread Starter

    Joined:
    Oct 1, 2001
    Messages:
    1,100
    In my work, I use the vlookup function all the time. Instead of typing vlookup all the time, I had a macro for one workbook to paste it in the cell by clicking on a tool button. (I used the 'record new macro' way of getting it to work in that one spreadsheet) When I work in another spreadsheet, the buttons there but it errors referring back to the origional workbook.

    How can I get that macro button to work along all spreadsheets in Excel?

    Thanks!
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    You hand typed in the function arguments within the function. It is going to refer back to the original worksheet/workbook since it is not relative to what workbook you put it in. Do both workbooks have the exact same layout?
     
  3. anglin_fool

    anglin_fool Thread Starter

    Joined:
    Oct 1, 2001
    Messages:
    1,100
    Basically the macro is just supposed to type in '=vlookup(' then I input the data to be looked up. Most of the time the left column will be what I am comparing but not always.

    nope, the layout is not always the same...

    Thanks,
     
  4. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Macros in Excel are picky. They want you to finish the function out before you can save them. This leads you to having problems since it will still be referencing the cells from the prior worksheet. What I would probably do since I do not know the code for it, is record a macro that you have typed out vlookup in. I would then go edit the edit macro and add the = before the text.
     
  5. anglin_fool

    anglin_fool Thread Starter

    Joined:
    Oct 1, 2001
    Messages:
    1,100
    naa, didnt like it...

    unfortunately it loos like I will be typing =vlookup( for a while. of course I could always paste it in.

    Any other ideas?
     
  6. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    There are a few possible, although none perfect that I can think of:

    1. First, and the way I use (Macros for Special tasks), is to have a common Macros Workbook ie. CommonMacros.xls, have it in a convenient place to open (like your Desktop), and open it each time you want to run your common Macros - always open it.

    Macros can work on other Workbooks as long as the Workbook with the Macros is open in the same session.

    2. Alternatively you could use a template for all your Excel - vlookup related documents; and you could even set-up your main template "book.xls" with the Macro, this would include the macro whenever you start a new workbook, although I wouldn't necessarily recommend this.
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The use of SendKeys is not recommended. For example:

    Sub Type_VLOOKUP()
    Sendkeys ("=VLOOKUP")
    End Sub


    Storing a macro in your personal macro workbook will make it available to all wbs. If you don't have a personal.xls, create one by recording something basic, specifying "store in: personal macro workbook". Then edit whatever you recorded to do what you actually want. (Note: you'll need to unhide personal.xls first to edit code stored in it).
     
  8. anglin_fool

    anglin_fool Thread Starter

    Joined:
    Oct 1, 2001
    Messages:
    1,100
    Thanks fellow TechGuy Donors,

    The MacroTasks.xls workbook idea was suggested by my office geek, umm coworker. Since I'm new to the company, I don't think I'll try not recommended things just yet.
     
  9. anglin_fool

    anglin_fool Thread Starter

    Joined:
    Oct 1, 2001
    Messages:
    1,100
    Grrrr, now its giving me a Signature errrrrrorrr!

    I came back the next day and opened the spreadsheet and I got this error.

    (Excel 2002 on a corporate server)
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/697589

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice