+ Reply to Thread
Results 1 to 9 of 9

Most Basic VLookup Info Needed

  1. #1
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Most Basic VLookup Info Needed

    I am having all sorts of problems trying to figure out VLookup in VBA.

    The data I am pulling from is in another workbook entirely. The column I am looking for is determined elsewhere in the module and saved on a working data spreadsheet. The biggest problem is that the VLOOKUP is looking for yesterday's date, then I need to pull data from that row in the given column AND from the next row in the same column.

    Please Login or Register  to view this content.
    I have never used VLookup in VBA before, and I'm pretty sure I just have the syntax wrong. The sources I usually use for formatting are of NO help this time.

    Also, any suggestions on how to get the value from the next cell down would be appreciated.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Most Basic VLookup Info Needed

    First, what is the problem with what you have? Are you getting a compile error? Runtime error? What error are you getting? Your code requires a very specific file configuration to work and we are not going to try to recreate it. You need to either provide the files, or give more detail about what isn't working the way you expect.

    Did you actually copy this code directly from the code editor? Because it should automatically capitalize Worksheets and Range in your VLOOKUP call. I want to make sure we have your exact code.

    As far as VLOOKUP, what data is in column A?

    Is workbook Sourcebook.xlsx open when you are trying to run this? If not, this will generate a runtime error.

    AND from the next row in the same column
    Nothing in your code appears to address this part of your problem. VLOOKUP can't help with this either. This requires a Find. Let's solve the first problem first then talk about this one.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Most Basic VLookup Info Needed

    OK, no the sourcebook isn't open, but I can do that. Done.

    Please Login or Register  to view this content.
    The first column of the data in the sourcebook.xlsx contains dates, but when I play around with formatting, they appear to be saved in Excel's datetime format.

    When I run this, I get a "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class" message

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Most Basic VLookup Info Needed

    When I run this, I get a "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class" message
    This usually means that your VLOOKUP() function returned an error, so I would suggest reviewing the VLOOKUP() function help file to understand what conditions cause it to return an error: https://support.office.com/en-us/art...8-93a18ad188a1 Towards the bottom of the help file is a list of conditions that trigger errors. The most common error is the N/A error if lookup value is not present in the lookup table -- especially when the 4th argument is FALSE (as in your case), when it will look for an "exact" match.

    Without the actual file, it will be difficult for us to debug this for you. If I were debugging this, I would put a "Stop" statement right before the lookup function
    Please Login or Register  to view this content.
    Then, run the procedure. When it enters debug mode, I would use the debugging tools (http://www.cpearson.com/Excel/DebuggingVBA.aspx probably create watches in the watch window) to determine which error condition exists in the VLOOKUP() function, probably starting with a very careful look at whether the "exact" value of date-1 is actually present in columnA of worksheets(1).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Most Basic VLookup Info Needed

    Quote Originally Posted by MrShorty View Post
    I would suggest reviewing the VLOOKUP() function help file to understand what conditions cause it to return an error: https://support.office.com/en-us/art...8-93a18ad188a1 Towards the bottom of the help file is a list of conditions that trigger errors.
    That is exactly the type of file I usually use for reference and couldn't find for VLOOKUP! Thank you. It probably won't be until tomorrow when I can have a look at it, but it should answer my questions.

    Thanks again!

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Most Basic VLookup Info Needed

    How about this.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Most Basic VLookup Info Needed

    Quote Originally Posted by bakerman2 View Post
    How about this.
    That looks like it might work. I don't see where you've declared the type of variable fRow is, and when I try it as an Integer, I get a "Tun-time error '13': Type mismatch" message when running the fRow = Application.Match(CDbl(Date - 1), .Columns(1), 0) line (yes, that is copied directly from my code).

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Most Basic VLookup Info Needed

    I'm not much into declaring variables but try fRow as Long (depending on the number of rows in your suorcefile)

  9. #9
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Most Basic VLookup Info Needed

    Perfect, thank you! (I feel stupid, but the problem was that the data wasn't on sheet 1 in the source file... *sigh*)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Basic Query, Please be Gentle.. charting secondary info
    By onedevilsstreet in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2015, 06:16 PM
  2. Basic Charting help needed
    By An album cover in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-16-2014, 10:46 AM
  3. Basic Macro needed
    By alanbenjy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-02-2010, 07:31 AM
  4. Basic IF function help needed
    By EME in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-29-2009, 05:51 AM
  5. Some basic (pun intended) info needed on VBA please
    By Bill Allen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2006, 12:55 AM
  6. Replies: 2
    Last Post: 06-28-2005, 12:49 PM
  7. Basic info I have forgotten....
    By bruce in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2005, 01:06 AM

Tags for this Thread

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