+ Reply to Thread
Results 1 to 9 of 9

VBA - Vlook up function not picking all values

  1. #1
    Registered User
    Join Date
    08-23-2019
    Location
    jaipur
    MS-Off Ver
    2
    Posts
    19

    VBA - Vlook up function not picking all values

    Guys, I have 2 workbooks
    I am trying to put vlook on the first book for the range which is present in second workbook
    First sheet named is macro
    second sheet named is BANKNIFTYWK29800CE
    so basically I want column G column of second sheet to come to first sheet on E column. for this first I joined date and time in both the sheets and then used vlookup.
    Vlook up is working fine till 1069 row. after this its giving me 0.05 value and after 1505 , its totally blank.

    I want vlookup to ignore the cell ( leave it blank) if the look up item is not there.

    I am attaching here both the workbooks.

    Thanks a lot guys
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VBA - Vlook up function not picking all values

    Show your formula please.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VBA - Vlook up function not picking all values

    I used this and got (apparent) valid returns for most of your date/times in C. the 1's that didnt give a return, gave error, presumably because that exact value doesnt exist in the lookup table. (Im not manually testing 1600+ entries lol)

  4. #4
    Registered User
    Join Date
    08-23-2019
    Location
    jaipur
    MS-Off Ver
    2
    Posts
    19

    Re: VBA - Vlook up function not picking all values

    please check this number
    30/07/20190.604861111111111

    its there in second file with a value, but in macro file, the value has come out to be 0.5 which is wrong
    also, for 01/08/20190.416666666666667 , the results are blank whereas 0.4 value is there in banknifty file.
    These 2 are just examples, there are many cases like this

  5. #5
    Registered User
    Join Date
    08-23-2019
    Location
    jaipur
    MS-Off Ver
    2
    Posts
    19

    Re: VBA - Vlook up function not picking all values

    Can someone look at it please ?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VBA - Vlook up function not picking all values

    Apologies, I forgot to add teh formula...
    =VLOOKUP(C2,'[BANKNIFTYWK29800CE (1).xlsm]BANKNIFTYWK29800CE'!B$1:I$3329,7,0)

    I also asked you to show your formula?

    And I cannot find 30/07/20190.604861111111111 in Macro file
    Last edited by FDibbins; 08-25-2019 at 12:48 AM.

  7. #7
    Registered User
    Join Date
    08-23-2019
    Location
    jaipur
    MS-Off Ver
    2
    Posts
    19

    Re: VBA - Vlook up function not picking all values

    sir, In second bank (BANKNIFTYWK29800CE)
    30/07/2019 14:31 30/07/20190.604861111111111 is 8.95
    but when I used vlookup in macro file, it is giving out to be 0.05

    also in second file 01/08/2019 09:15 01/08/20190.385416666666667 is 0.3
    but in macro file, its completely blank

    Please let me know if it makes sense. I am using it in VBA and the macro is there in the excel attached
    Thanks
    Last edited by AliGW; 08-25-2019 at 03:17 AM. Reason: Please don't quote unnecessarily!

  8. #8
    Registered User
    Join Date
    08-23-2019
    Location
    jaipur
    MS-Off Ver
    2
    Posts
    19

    Re: VBA - Vlook up function not picking all values

    Not sure why this is happening.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VBA - Vlook up function not picking all values

    I understand that you are asking about doing this with VBA, but have you considered just using a simple vlookup formula in the sheet itself?

+ 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. Vlook up function support
    By Iain170 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2017, 09:29 AM
  2. vlook up help and maybe if function as well
    By Scarey3 in forum Excel General
    Replies: 4
    Last Post: 04-02-2016, 11:10 AM
  3. Using If and Vlook function with VBA
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-22-2013, 02:47 PM
  4. [SOLVED] Vlook Up Function
    By potsie via OfficeKB.com in forum Excel General
    Replies: 1
    Last Post: 04-23-2006, 10:58 PM
  5. [SOLVED] Picking values out
    By Jez in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-06-2006, 05:25 AM
  6. [SOLVED] If Function containing VLook up
    By Michele in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-06-2005, 05:09 PM
  7. picking out certain values
    By Wes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2005, 04:06 AM

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