VLookup Data Woes (XL 97 SP2)

Topic

New Reply

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

Reply | Quote

Replies

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,’Access 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

Reply | Quote

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….

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

Great. I should have mentioned checking your actual cell values, but I just assumed you had.

Reply | Quote

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

Reply | Quote

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

Reply | Quote