+ Reply to Thread
Results 1 to 6 of 6

Incorrect calculation of Vlookup value when running Macro

  1. #1
    Registered User
    Join Date
    06-25-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    4

    Incorrect calculation of Vlookup value when running Macro

    Hi

    I am running a macro that, among many thing, has to vlookup values for a massive table (about 60,000 cells to calculate).

    After running the macro, I saw that some values had calculated to #N/A even though there were definitely values for their indexes to display. Afterwards I double and triple checked that these values should not have displayed as #N/A.

    I then went through the code line by line and this time all the values calculated 100% correct.

    My question:
    Is it possible that the macro is continuing with the next line of code while all the cells have not completed calculating their Vlookup values and hence certain cells then display #N/A?

    If this is the case, is there a way that I can put a 'pause' after the vlookup to give Excel enough time to calculate all the cells?
    Last edited by Kreef; 06-25-2011 at 08:24 AM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Incorrect calculation of Vlookup value when running Macro

    Hi and welcome to the board.
    I suggest you post your macro so that the VBA guys can have a look at it

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Incorrect calculation of Vlookup value when running Macro

    Why are using VLOOKUP in the code?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    06-25-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Incorrect calculation of Vlookup value when running Macro

    Thanks for the welcome.
    Even though the code is VERY simple and VERY untidy, I don't think the problem is with the code. But here it is anyways.

    First it puts the formulas in an empty row. This row then obviously calculates all the values to #N/A

    Please Login or Register  to view this content.
    I then copy the formulas that have been entered into row 35,001 and paste it in the area where I need the values. This area consists of several rows that have been filtered using Autofilter. I thus only paste it into the visible rows.

    It then calculates for quite some time. After this I delete the formulas from row 35,001

    Please Login or Register  to view this content.
    I make all rows visible again. Select all cells and Special paste them as values to remove the formulas.

    Please Login or Register  to view this content.

    At the end of all of this, the area contains the following:
    • Rows were columns L to AB are empty (this is correct)
    • Rows were columns L to AB contain the values that were on sheet Prev_Orderwell (this is correct)
    • Rows were columns L to AB contain #N/A where the index in column C did NOT have any corresponding data in Prev_Orderwell (this is correct)
    • Rows were columns L to AB contain #N/A where the index in column C DID have corresponding data in Prev_Orderwell (MY PROBLEM)

    When I paste the formulas from row 35,001 into the area, the values that are brought with the formulas is #N/A. It seems that not all the rows are getting time to recalculate before I copy the entire area and pastes it as values.

    Any thoughts?

  5. #5
    Registered User
    Join Date
    06-25-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Incorrect calculation of Vlookup value when running Macro

    Quote Originally Posted by royUK View Post
    Why are using VLOOKUP in the code?
    Because I am still a beginner and I do not know how else to do it. It is working for the purpose that I need it, but it is not working on all the rows...

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Incorrect calculation of Vlookup value when running Macro

    I think you would find the .Find Method for VBA much better, see the VBA Help Files.

    It would help if you attached an example workbook

+ 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