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

    Re: VLookup Data Woes (XL 97 SP2)

    Sounds to me as though Excel is not sure what format the conflictting cells are in. Editing forces Excel to "take a closer look". What you might try to do is run this code on the sheet:

    For each oCell in Activesheet.Usedrange.Cells
    oCell.value=oCell.Value
    Next
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLookup Data Woes (XL 97 SP2)

    Hi All!

    I am hoping someone else has run into this at some time or another and can verify with me that either there is a good solution or it's just a MS feature that I need to find some way to build around. I'm usually in the Access or Word forums, so I'm a bit new to Excel, so please be gentle ;-)

    What I have is a set of Excel workbooks that someone else built, and they have lots of nice VLookups in them. The last sheet in the workbook is always a data sheet. What these guys are doing is getting the data from an Access query, putting it in Excel (save to Excel), then manually copying it over (after manipulating it a bit). I'd like to have a more automated way of doing this, but here's my problem:
    The workbooks work just fine if you copy data into the worksheet, but if you use either MS query or a linked table, all of a sudden the workbooks randomly don't work. I can assure you that it's the exact same data whether you copy it or query it. What is the strangest part of it is the randomness with which the Vlookup doesn't work.

    I have a series of codes that are being looked up:
    Code Hours
    Adm01 12
    Adm03 15
    AdmLv01 42
    App02 7
    Here is the lookup code:
    +IF(ISERROR(VLOOKUP('Exams'!B8,'Access Data'!A:C,3,FALSE)),"0.00",VLOOKUP('Exams'!B8,'Acc ess Data'!A:C,3,FALSE))

    When I use MS Query and return the data, the lookups for Adm03 and AdmLv01 work just fine. Adm01 and App02 do not work at all (they return 0.00 instead of the hours). If I go in and re-type Adm01, all of a sudden it works. (There are about 142 codes; about 70% work just fine, about 30% don't work until they're re-typed.) If the whole thing didn't work, I could say it might be a format problem, but it's so random that it can't be that.

    Ideally I would like to link from Excel to Access so that the data is live, but the link randomly doesn't work in the same way. I've already checked to make sure the sort is correct, so it's not that.

    Is this a MS feature, or is there something I can do to fix it?

    TIA!

    Cecilia :-)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Data Woes (XL 97 SP2)

    Cool, most definitely worth a try--Can you give me an idea which event or object to put this code under? I'm not at all an Excel developper.... ;-)

  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 Data Woes (XL 97 SP2)

    Well, you said something about automating the refresh of the data, so I assumed you already had some code doing that.

    If not, simply copy my code into a normal module (In the VBE: Insert, module).

    Start by typiong the word "Sub" followed by the name you want it to have, e.g:

    Sub EditAndEnter()
    ' paste my code here
    End Sub
    Now after refreshing, run my code while you are on the sheet that contains the faulty data (attach macro to e.g. a toolbar button or press alt-F8 and select the macro)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Data Woes (XL 97 SP2)

    Ah! I just figured it out!

    The shorter codes had two blank spaces at the end. I thought I had originally checked this, but evidently I didn't do it twice ;-)

    Thanks for reading my hopeless pleas and for your advice ;-)

    Cecilia :-)

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

    Re: VLookup Data Woes (XL 97 SP2)

    Great. I should have mentioned checking your actual cell values, but I just assumed you had.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Data Woes (XL 97 SP2)

    Hee hee, I thought I had too! (guess not enough, though) Just goes to show you that REBOOT really does work! *g*

  8. #8
    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 Data Woes (XL 97 SP2)

    When I import data, for all columns with alpha data I insert a column to the right and then use the =TRIM(B1) command and copy down. This strips out all blanks appended by Excel when the data is shorter than the length of the data field. I then copy that new column, paste special - data, to remove the command and then delete the original column.
    A macro would do this for you to automate the process. HTH
    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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