Results 1 to 7 of 7
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    VLOOKUP Anomalies (97 SR2)

    A customer sends us their orders in CSV format. I created a template where macros open the CSV file, formats the data and save it as .xls. The xls data is then transferred to summary sheets. The data in the summary sheet is then cut and pasted into an overall sheet where a Vlookup picks up the customer store code and adds our customer code. The lookup table is held in another file in the same directory.
    From time to time this lookup table file is updated and the new files distributed to users.
    The problem I have is that the lookup value returned is randomly a value that was in the old lookup table, or #N/A. If you click on the formula bar and press enter, then the correct value is returned.! Links shown in he workbook are correct. Excel has been re-installed.
    Can anyone shed any light on possible sources of this problem, as I have searched for other instances of the lookup file on the C disk, without success.
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Anomalies (97 SR2)

    My guess is that XL does a poor job recalculating here. Maybe good to include this little sub at the end of your manipulation code:

    Sub CalcFullXL97()
    Sendkeys"^%{F9}"
    DoEvents
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: VLOOKUP Anomalies (97 SR2)

    Thanks Hans, I will add your code to see if this will cure the user's problem.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Anomalies (97 SR2)

    Hans??? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP Anomalies (97 SR2)

    Those Dutchmen are all the same - it's impossible to distinguish them. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  6. #6
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: VLOOKUP Anomalies (97 SR2)

    Omigosh, why did I do that?? <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>
    My apologies Jan for the wrong name <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP Anomalies (97 SR2)

    No problem, Jim I never make a mistake like that... <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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