At work I've got a mileage chart with distances between around 90 resorts.
I've already got a userform set up to retrieve the distance between 2
selected resorts. What I'd like to add is the ability to list at the bottom
of the user box the ten closest resorts to the first chosen resort.

Logic says that I would have to pull a list of all of the distances from the
chosen resort, sort it, then get the top ten onto the userform. Although, I
haven't been able to get anything to work yet.

Your help is much appreciated.

Your description is a little thin but you can look at the LARGE worksheet
function which returns the ? largest value in an array of values...
--
HTH...

Jim Thomlinson


"JNW" wrote:

At work I've got a mileage chart with distances between around 90 resorts.
I've already got a userform set up to retrieve the distance between 2
selected resorts. What I'd like to add is the ability to list at the bottom
of the user box the ten closest resorts to the first chosen resort.

Logic says that I would have to pull a list of all of the distances from the
chosen resort, sort it, then get the top ten onto the userform. Although, I
haven't been able to get anything to work yet.

Your help is much appreciated.

OK, to clarify.

-I have a mileage chart giving distances (in miles) between resorts owned by
the company I work for.
-I would like to create a macro that returns the 10 closest resorts to a
chosen resort.

-The combo box that users select the chosen resort from is on a userform
-I would like the results to appear on the userform as well (either in
textboxes or as labels)
-The mileage chart is a 90x90 grid in a spreadsheet.

Let's say that 'Las Vegas' is the chosen resort... Upon chosing this result
the macro would look in the row containing all of the corresponding
distances, determine the 10 shortest distances, match those distances to the
corresponding resorts, and display them on the the userform.

Thanks again in advance.

"Jim Thomlinson" wrote:

Your description is a little thin but you can look at the LARGE worksheet
function which returns the ? largest value in an array of values...
--
HTH...

Jim Thomlinson


"JNW" wrote:

At work I've got a mileage chart with distances between around 90 resorts.
I've already got a userform set up to retrieve the distance between 2
selected resorts. What I'd like to add is the ability to list at the bottom
of the user box the ten closest resorts to the first chosen resort.

Logic says that I would have to pull a list of all of the distances from the
chosen resort, sort it, then get the top ten onto the userform. Although, I
haven't been able to get anything to work yet.

Your help is much appreciated.

How do you want to handle ties?
--
Jim


"JNW" wrote:

OK, to clarify.

-I have a mileage chart giving distances (in miles) between resorts owned by
the company I work for.
-I would like to create a macro that returns the 10 closest resorts to a
chosen resort.

-The combo box that users select the chosen resort from is on a userform
-I would like the results to appear on the userform as well (either in
textboxes or as labels)
-The mileage chart is a 90x90 grid in a spreadsheet.

Let's say that 'Las Vegas' is the chosen resort... Upon chosing this result
the macro would look in the row containing all of the corresponding
distances, determine the 10 shortest distances, match those distances to the
corresponding resorts, and display them on the the userform.

Thanks again in advance.

"Jim Thomlinson" wrote:

Your description is a little thin but you can look at the LARGE worksheet
function which returns the ? largest value in an array of values...
--
HTH...

Jim Thomlinson


"JNW" wrote:

At work I've got a mileage chart with distances between around 90 resorts.
I've already got a userform set up to retrieve the distance between 2
selected resorts. What I'd like to add is the ability to list at the bottom
of the user box the ten closest resorts to the first chosen resort.

Logic says that I would have to pull a list of all of the distances from the
chosen resort, sort it, then get the top ten onto the userform. Although, I
haven't been able to get anything to work yet.

Your help is much appreciated.

I did somewhat of a work around. It's not as clean as I'd like it, but it
works. Let me know if there is anything that can be done to make this more
succinct.


My steps:
-Resort is selected from combo box
-HLookup function in worksheet to return all distances to other resorts
-"=IF(E2120,E212,"")" Next to above results to get rid of overseas resorts
that have a zero balance.
-then I used the SMALL function to pick out the 10 smallest values.

-In a userform I've got the combo box with a button next to it.
-the following headers (rank, resort, miles)
-Then when the resort is chosen and the button pushed the following code
executes:

Private Sub ButtonCalc_Click()
On Error GoTo ErrRoutine
With Sheets("Mileage Info")
ClosestResort01.Caption = .Range("M212").Value
ClosestMiles01.Caption = .Range("L212").Value
ClosestResort02.Caption = .Range("M213").Value
ClosestMiles02.Caption = .Range("L213").Value
' ' continues for all ten top results......
End With
ErrRoutine:
If Err.Number = "13" Then
MsgBox "There is no data for this location at the moment. Please
choose another location"
Exit Sub
End If
End Sub



"JNW" wrote:

At work I've got a mileage chart with distances between around 90 resorts.
I've already got a userform set up to retrieve the distance between 2
selected resorts. What I'd like to add is the ability to list at the bottom
of the user box the ten closest resorts to the first chosen resort.

Logic says that I would have to pull a list of all of the distances from the
chosen resort, sort it, then get the top ten onto the userform. Although, I
haven't been able to get anything to work yet.

Your help is much appreciated.