Macro for variable location data

[LLuvin]

So let’s see how can I explain this.

I’m fairly new to VBA and macros, and I think I’m trying to do something complex for me at the moment.

I have the data that I want in a .html file. If I open that with excel, all data appears in column A, but I’m only interested in the data regarding Banana’s spots. Because there is nothing that appears only once, I need to find “This is what I want” in that column, and I want to copy the next 18 rows from there and paste it in A4 (because I have a formula in other cells that use the data from the cells I’m pasting it into)

The problem that I’m having is that I have 2 types of bananas, and they are in different folders, so it’s a variable location.

So the 2 types are Banana and Banana+

Banana data is in: \\folder\Quality\banana\v9.9\Completed then there is a folder per lot of banana (i.e. Lot 123) and inside that folder there is a results folder, and inside that folder is the Report.html

The same goes for Banana+, the data is in: \\folder\Quality\banana+\v.2.1\Completed then the same as before, a folder per lot with a data folder, a results folder, and the Report.html inside.

Hope everything make sense.

This is where I got to so far

Sheet is called “Fruit”
lotnumber is in cell B1
path is in cell F1 (that comes up after you select the banana type from a drop down)
the path that I have written in that cell is
\\folder\Quality\banana\v9.9\Completed
\\folder\Quality\banana+\v2.1\Completed

Please Login or Register  to view this content.

I'm getting an error in the filename path

Any ideas?

Thanks a lot!

[pjwhitfield]

What is the error youre getting and which line of code is highlighted when it debugs?

Also can you post what exactly is shown in cell F1?