VLOOKUP Anomalies (97 SR2)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

Thanks Hans, I will add your code to see if this will cure the user’s problem.

Reply | Quote

Hans???

Reply | Quote

Those Dutchmen are all the same – it’s impossible to distinguish them.

Reply | Quote

Omigosh, why did I do that??
My apologies Jan for the wrong name

Reply | Quote

No problem, Jim I never make a mistake like that…

Reply | Quote