VBA code/array to solve data set

[mjhd]

Hi!

I have very limited VBA skills but thought of a cool scenario that I was thinking of for my trip. I would like to know how you go about this or if it even possible in VBA, as I can only do very basic things.

I have to travel to 4 cities. In those 4 cities are 3-4 hotels that I can stay in. It does not matter how many nights I stay in each city, but I must visit all 4 cities. It also does not matter which hotel I use by city, but I must use each hotel at least once (in total)- and I also must stay a minimum amount of nights by hotel (in total, table2). I must use up all those nights (constraint). I was wondering if VBA can deliver the cheapest scenario based in the numbers in table:

Table1

CITY HOTEL COST
LA Hilton 167
LA Marriot 174
LA Holiday Inn 149
LA Sheraton 162
NYC Hilton 212
NYC Marriot 199
NYC Holiday Inn 133
NYC Sheraton 168
Chicago Hilton 124
Chicago Marriot 149
Chicago Sheraton 127
Dallas Hilton 134
Dallas Marriot 146
Dallas Holiday Inn 96
Dallas Sheraton 128


Table2

HOTEL NIGHTS
Hilton 4
Marriot 6
Holiday Inn 5
Sheraton 3

Any help would be great!

 

[JHB]

... if it even possible in VBA, as I can only do very basic things.
..

If it can be done on paper, it can be done by using VBA, the question is if you can do it by your limit skills in VBA.
So either you must pay someone to write the code, or you must wait for if a friendly member pass by your thread and maybe will write the code for you.
Initially you need to find out which hotels is only present in certain cities.
Because if only one hotel is available in a city, you've to book that hotel and then it is excluded of booking in the other cities.

 

[mjhd]

If it can be done on paper, it can be done by using VBA, the question is if you can do it by your limit skills in VBA.
So either you must pay someone to write the code, or you must wait for if a friendly member pass by your thread and maybe will write the code for you.
Initially you need to find out which hotels is only present in certain cities.
Because if only one hotel is available in a city, you've to book that hotel and then it is excluded of booking in the other cities.

Thanks, yes- the hotels I listed with the city are the ones available in those cities.

 

[stopher]

If I read the problem right, then this is easy to do in your head...

Let's take the Hilton. There are 4 Hilton hotels. And I have to stay in a Hilton for a minimum of 4 nights. So I just stay one night in each - job done. There's no point staying in any Hilton any longer because I'm not required to and to do so would put the cost up.

Next take the Marriot. There are 4 hotels so I must stay in each one at least one night. That's 4 nights of the 6 require nights used up. So which Marriot do I stay in for the two remaining nights? Well it's got to be the cheapest one (Dallas). To stay in any of the others would just be more expensive. And again staying more that the require nights is just more unnecessary expensive.

And so on...

So I guess the logic is...

For each hotel chain,
- Allocate 1 night to each hotel in the chain
- calc the remaining nights
- add the remaining nights to the cheapest hotel in the chain
next hotel chain

 

[jdraw]

Sounds like a homework problem? I agree, with pencil and paper identify the constraints/rules, the hotels and cities, then some logic (series of if's or loops) to match the rules.

 

[mjhd]

If I read the problem right, then this is easy to do in your head...

Let's take the Hilton. There are 4 Hilton hotels. And I have to stay in a Hilton for a minimum of 4 nights. So I just stay one night in each - job done. There's no point staying in any Hilton any longer because I'm not required to and to do so would put the cost up.

Next take the Marriot. There are 4 hotels so I must stay in each one at least one night. That's 4 nights of the 6 require nights used up. So which Marriot do I stay in for the two remaining nights? Well it's got to be the cheapest one (Dallas). To stay in any of the others would just be more expensive. And again staying more that the require nights is just more unnecessary expensive.

And so on...

So I guess the logic is...

For each hotel chain,
- Allocate 1 night to each hotel in the chain
- calc the remaining nights
- add the remaining nights to the cheapest hotel in the chain
next hotel chain

Hi,

That would be a solution, but it would not necessarily be the cheapest most optimal solution. what I'm interested in is the most cheapest solution to solve.

FYI I have solved by using Solver of course in Excel, and if anyone wants to know how to solve this using Solver I can certainly help, but I just thought that code would be cool to solve this.

 

[mjhd]

Sounds like a homework problem? I agree, with pencil and paper identify the constraints/rules, the hotels and cities, then some logic (series of if's or loops) to match the rules.

Hi,

Yes- as mentioned in the original post the constraints is basically table2, must meet those numbers by hotel. And must stay in each city at least once. My previous post mentions that using Solver I can complete this, but was seeking code to do this as it would very beneficial to learn from and cool to apply.

 

[MarkK]

It also does not matter which hotel I use by city, but I must use each hotel at least once (in total)

This, IMO, is unclear. Does "each hotel" refer to the chain, or to each city's location?

 

[mjhd]

This, IMO, is unclear. Does "each hotel" refer to the chain, or to each city's location?

Refers to the chain. So Sheraton, for example, can be 0 days in 3 of the 4 cities, just needs to be used at least once in any city with the total number of days from table2 fulfilled.

 

[stopher]

Refers to the chain. So Sheraton, for example, can be 0 days in 3 of the 4 cities, just needs to be used at least once in any city with the total number of days from table2 fulfilled.

The problem makes more sense now. So this is a Linear Programming problem and you used Solver (LP Simplex) to solve. So yes you can write VBA to solve LP problems. It's easy to write such code in VBA. What is not so easy is understand the LP Simplex algorithm in order to write the code. Probably easier to find the algorithm that someone has written in C and convert it. Not too difficult.

 

[plog]

I would like to know how you go about this or if it even possible in VBA, as I can only do very basic things...
That would be a solution, but it would not necessarily be the cheapest most optimal solution. what I'm interested in is the most cheapest solution to solve....

What class is this for exactly? I don't mind helping with homework, but I'm not doing the assignment for you. I just can't tell if this is a VBA homework assignment, a Programming 101 assignment or some sort of algorithm class which requires you to just describe a method for solving.

So come clean with what this class is for and also post what your thoughts are on solving this.