+ Reply to Thread
Results 1 to 15 of 15

Vlookup works while writing macro - gives err msg when executed

  1. #1
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Vlookup works while writing macro - gives err msg when executed

    I have wriiten a macro, in which I execute a vlookup. It works fine while I am recording the macro but when I execute it I am given #N/A errors. Before stopping the recorder I copied - paste - value so it would not need the vlookup formula. Then I moved and hid the columns the database and column 1 were in.

    Here is the code below.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Vlookup works while writing macro - gives err msg when executed

    Hi,

    You need to set a breakpoint in your code and step through it to discover what is wrong.
    See http://www.cpearson.com/excel/Debug.htm

    If the VLookup doesn't work it is because something has moved or isn't there. Stepping through your code and looking to see what is happening is my best suggestion for solving your problem.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Vlookup works while writing macro - gives err msg when executed

    Quote Originally Posted by MarvinP View Post
    Hi,

    You need to set a breakpoint in your code and step through it to discover what is wrong.
    See http://www.cpearson.com/excel/Debug.htm

    If the VLookup doesn't work it is because something has moved or isn't there. Stepping through your code and looking to see what is happening is my best suggestion for solving your problem.
    Please Login or Register  to view this content.
    I have highlighted the problem part of the code. Can you please tell me how to write activesheet instead of sheet2?
    Last edited by NBVC; 08-30-2011 at 10:01 AM.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Vlookup works while writing macro - gives err msg when executed

    Do you already have a name of DATABASE4 defined? Are you trying to resize it instead of adding it again?

  5. #5
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Vlookup works while writing macro - gives err msg when executed

    I have been using CITYDATABASE. Maybe that's a problem also; I have been using a name other than DATABASE4 and maybe that's what the vlookup is looking for. Thanks for your help

  6. #6
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Vlookup works while writing macro - gives err msg when executed

    In the latest macro, I have been consistently using CITYDATABASE and it is defined as a name. I thnk the problem is that the macro has been looking sheet2 instead of the activesheet.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Vlookup works while writing macro - gives err msg when executed

    It just occured to me there are two kinds of Named Ranges. See the SCOPE section in
    http://www.cpearson.com/excel/DefinedNames.aspx

    If you were to define a range on Sheet1 and then try to address it, thinking it was on Sheet2, you might get the error.

  8. #8
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Vlookup works while writing macro - gives err msg when executed

    That's why I want to use the active sheet, so no matter what sheet I am on the macro will work. thanks

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Vlookup works while writing macro - gives err msg when executed

    In your code posted above you have a square bracket just before
    Please Login or Register  to view this content.
    Is that the problem?

  10. #10
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Vlookup works while writing macro - gives err msg when executed

    The bracket does not appear in my latest code. I think the problem is when I first created the macro I did it on Sheet2. Since that time I am Sheet2 is closed and I am not tyring to execute the macro and I think it is looking for Sheet2, thus the #N/A errors.

  11. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Vlookup works while writing macro - gives err msg when executed

    I've re-read your original question again and wonder if you can select a cell that is hidden. The bottom line is "what is causing the #N/A error". OR What have you done manually that is keeping your macro from working like you want.

    This is why I wanted you to step through the code and see each line. Many times macros are dependent on the sheet you are on, being correct, for them to do what you want.

    Read http://www.cpearson.com/excel/Debug.htm for some good techniques to find why things don't work like you want.

    I guess to answer the question completely I'd need a sample with the workbook and code to step through it myself.

  12. #12
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Vlookup works while writing macro - gives err msg when executed

    I did do as you suggested and go through the code, step by step, that's how I know the problem involves the sheet name. I have attached the file. Ctrl + r will start the macro. thank you very much for your help.
    Attached Files Attached Files

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Vlookup works while writing macro - gives err msg when executed

    Hi,

    I think the problem is with your define of named range. Read this
    http://www.cpearson.com/excel/DefinedNames.aspx
    The section about Global vs Sheet named ranges. I don't think you can use ActiveSheet.Name in your code.

    Your code keeps crashing on me and I get a dialog box to select a filename. I'm a little stuck on this one.

    In reading further down the above link I believe the answer lies. You need to set a name variable to the name before using it in the formula.
    Last edited by MarvinP; 08-30-2011 at 11:27 AM.

  14. #14
    Forum Contributor
    Join Date
    08-08-2011
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Vlookup works while writing macro - gives err msg when executed

    Can you illustrate how this is done? I am totally new to VBA. I do plan to take a class.

  15. #15
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Vlookup works while writing macro - gives err msg when executed

    My best advice is to read the link I suggested above.
    In the last section about "Working With Names In Visual Basic" should give you some ideas.

    It is really hard to work through your recorded macro as it changes what it starts with. This makes it impossible to run it again after it has run the first time, without starting over from scratch.

    I agree that the named range and then using it in the VLookup formula is the problem for now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1