Dynamic Workbook path

[Ruca13]

Hello everyone

The purpose of what I'm trying to build is to get the price at a certain location and at a certain week.

For now my code is:

Sub Price()


price_path = Worksheets("Settings").Cells(1, 11).Value


'Workbooks.Open(price_path &


    Columns(50).Select
    Selection.Find(What:=Cells(ActiveCell.Row, 18), After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate


End Sub

Where the active cell is where I wanna put the price, the column 18 with the active cell row gives the day of the occurence, and I'm finding it in a table in which I have all the days of 2016, next the price week, and of this on separate columns: day, month, year, month in text (ex:jan), month in text2 (ex:January).

The first thing will be to open the correct file, that after the price path should be year\month year, and only after that is the file, which indicates the price week (a date that holds for the whole week) (ex: 2016\01 January 2016/FPI 01 Jan 2016.xlsx , which is way I have the months written in text, and the numbers part is formatted to read as 0#).

The opened file will have a list of locations, prices and suppliers.

After that, I want to find the location (activecell.row, col16) for which the price stands. The header for the location in the price files is cell B8, supplier M8 and price K8. If the price is NA but the location exists, a message will appear that no price is available (i think i need to *1 and use iserror).

Now for the two most complicated parts.

• If the location does not exist is the opened file, then it must open the previous file, and so on until it finds the location.

• If there is more than one occurrence for the location (two prices for the same location), I want an user form that gives me the option to choose the price and supplier I want

I'm sorry it's a bit of a mess and probably a lot of work, but thank you to whoever can help.