+ Reply to Thread
Results 1 to 13 of 13

Vlookup VBA based on date

  1. 02-15-2009, 11:43 PM #1
    Rawlings99
    Rawlings99 is offline
    Registered User
    Join Date
    02-15-2009
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    8

    Vlookup VBA based on date

    Hi All,

    I'm having trouble getting my vlookup formula to work in vba. Basically, I have a range of dates in column A and want to the vlookup to fill these in until there are no dates available. The reason I want to do this in VBA opposed to on the spreadsheet, is that I have large subset of time series data which needs to be frequently updated and wish for the data in column C to be updated everytime the macro is run.

    My code is below. I keep on getting a runtime error 13 on the highlighted line below. Any input would be great!

    Please Login or Register  to view this content.
    Last edited by Rawlings99; 02-16-2009 at 01:18 PM.
    Reply With QuoteRegister To Reply
  2. 02-16-2009, 12:23 AM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Vlookup VBA based on date

    Welcome to the forum.

    Hard to guess what's wrong without seeing the workbook.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 02-16-2009, 12:37 AM #3
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: Vlookup VBA based on date

    Perhaps this will fix your problem..
    Please Login or Register  to view this content.
    - Paul

    Click here to read the Forum Rules

    Reply With QuoteRegister To Reply
  4. 02-16-2009, 12:38 AM #4
    Trebor76
    Trebor76 is offline
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,565

    Re: Vlookup VBA based on date

    Hi there,

    Agree with shg but it may be that there's a text entry in cell A1 and not the date entry the code is expecting to find based on the variables you've declared.

    HTH

    Robert
    Reply With QuoteRegister To Reply
  5. 02-16-2009, 12:48 AM #5
    Rawlings99
    Rawlings99 is offline
    Registered User
    Join Date
    02-15-2009
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    8

    Question Re: Vlookup VBA based on date

    Quote Originally Posted by pjoaquin View Post
    Perhaps this will fix your problem..
    Please Login or Register  to view this content.
    For some reason, I tried this and seem to be getting the following new error:

    Unable to get the Vlookup property of the worksheetfunction class. I know the code has been changed, but to respond to Robert, all the data points in column A are definitely dates. I changed the format to currency for a moment to confirm this. Also, this vlookup works perfectly fine on the spreadsheet just not in VBA

    If needed, is it possible to for me to post a copy of a virtual spreadsheet on this forum?
    Reply With QuoteRegister To Reply
  6. 02-16-2009, 12:58 AM #6
    Chris Bode
    Guest

    Re: Vlookup VBA based on date

    Well, it is quite hard to tell without looking the work book, though I prefer you to use following code instead

    Please Login or Register  to view this content.

    Hope this works

    Have a nice time

    Chris
    Last edited by royUK; 02-28-2009 at 02:15 PM.
    Reply With QuoteRegister To Reply  
  7. 02-16-2009, 01:02 AM #7
    Paul
    Paul is offline
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: Vlookup VBA based on date

    Instead of "WorksheetFunction", perhaps try "Application.WorksheetFunction". It can be fickle, although it seems to work fine on my magical PC.

    Reply With QuoteRegister To Reply
  8. 02-16-2009, 01:21 AM #8
    Rawlings99
    Rawlings99 is offline
    Registered User
    Join Date
    02-15-2009
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Vlookup VBA based on date

    Hmm, good suggestions but these don't seem to be working. For simplicities sake, I've created a simple workbook ( which is much simpler than the massive workbook I'm working with but similar in theory) and uploaded it to this attachment. The dates to be looked up are in column A. The "price list" in a column B, and the data should loop through until the final date is selected. A wrinkle to add: This data source is frequently updated and should be able to update every time the macro is run without any adjustments.

    Let me know if this is feasible.

    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-16-2009, 03:48 AM #9
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Vlookup VBA based on date

    There are a few issues with your code other than the date VLookup but to answer that question:

    When looking for a Date type variable against a range containing dates you should use the Long equivalent of the Date:

    Please Login or Register  to view this content.
    You will still need to cater for the possibility that the VLookup may fail (ie date not in the range).
    Last edited by DonkeyOte; 02-16-2009 at 03:52 AM.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  10. 02-16-2009, 03:55 AM #10
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Vlookup VBA based on date

    An alternative approach would be along the lines of:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 02-16-2009, 12:32 PM #11
    Rawlings99
    Rawlings99 is offline
    Registered User
    Join Date
    02-15-2009
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Vlookup VBA based on date

    [QUOTE=DonkeyOte;2043826]There are a few issues with your code other than the date VLookup but to answer that question:

    When looking for a Date type variable against a range containing dates you should use the Long equivalent of the Date:

    Please Login or Register  to view this content.
    Thanks for the input
    I think we are getting closer but I am still getting a type mismatch error on that line. Is that what you meant? Second does CLng change the cell type to long?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 02-16-2009, 12:39 PM #12
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Vlookup VBA based on date

    Based on the data I saw i should be 2, if it's not numeric value you will get a mismatch and row 1 is text as I recall... you will also get a bug if the lookup_value can't be found in the named range so you will need to add a handler or adopt a different approach (see my 2nd post).

    I would also argue that both i & col should be Declared as Long rather than Integer...

    And yes CLng will convert the date value to Long (in XL dates are integers - ie today is equiv. of 39860 (assumes 1900 date system in operation))
    Reply With QuoteRegister To Reply
  13. 02-16-2009, 01:17 PM #13
    Rawlings99
    Rawlings99 is offline
    Registered User
    Join Date
    02-15-2009
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Vlookup VBA based on date

    Great Thank you very much! This has been solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1