vlookup macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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?
 

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,
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 

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?
 
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.
 
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).
 

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.
 

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)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top