Hello everyone.

I'm trying to obtain a price for a certain location at a certain date, that should be in one of different db of prices. There are probably files for every week, and on different folders.

I choose the cell where i want the price, click the header (it has a button), and then it should open the file based on the date on that row (to be more accurate, the price week for that row date). The date is inserted above a table, that has formulas allowing me to retrieve the information needed for the path and file names.

I am able to open the (supposedly) correct file and vlookup the price.

My problems are:
  • The price i'm retrieving is coming without the decimal dot. So a price that should be like $1.43 reads 143###.
  • If the location exists in the file, but the price is NA, put a message box (I'm hoping that at least this I get on my own, but for now, it's also something to be resolved)
  • If the location does not exist in the file with the correct date, then it should search in the file from the week before - and so on. The first file should allways be something like FPI 01 xxx 2016.xlsx, so if it does not found there the location, then search in the folder before.
  • If more than one location is found in the file, and for that it must search using left and len = 4 (because some locations can be like ICAO and ICAO1, which is only a mean to distinguish them), then a User form will pop up and allow me to choose the price I want from the ones existing.


For now my code is this:
Please Login or Register  to view this content.
Please let me know if any additional information is needed.

Thank you for your help.