Hi All,

I have a large mileage spreadsheet that I use at work (a small exampl
of which appears below with letters A through D substituting for th
real names). My problem is that I want to either have a userform o
use a formula to be able to return the distance between two places

eg Place A - Place C = 8 miles, or Place C to Place D = 7 miles.

Ideally I'd like to have 2 controls that the user completes with th
name of start point and end point and the code (or function) shoul
return the distance between the two.

I think the formula should be something like LOOKUP but I don't eve
know where to start.

I'd be really grateful if someone could point me in the righ
direction



A B C D
A * 5 8 9
B 5 * 3 4
C 8 3 * 7
D 9 4 7 *

Thanks in advance

Seamu

--
Message posted from http://www.ExcelForum.com

Seamus,
Improve on this
City names are in Range("A2:A8") and Range("B1:H1").
search city names are in Range("M3") and Range("N3").
HTH
Cecil

Sub ShowDistance()
Range("A2:A8").Select
Selection.Find(What:=Range("M3").Value, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set rngh = ActiveCell.EntireRow
Range("B1:H1").Select
Selection.Find(What:=Range("N3").Value, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set rngv = ActiveCell.EntireColumn
Set isect = Application.Intersect(rngh, rngv)
MsgBox ("Distance from " & Range("M3").Value & " to " & _
Range("N3").Value & " is " & isect.Value & " Miles.")
End Sub

"SOS " wrote in message
...
Hi All,

I have a large mileage spreadsheet that I use at work (a small example
of which appears below with letters A through D substituting for the
real names). My problem is that I want to either have a userform or
use a formula to be able to return the distance between two places

eg Place A - Place C = 8 miles, or Place C to Place D = 7 miles.

Ideally I'd like to have 2 controls that the user completes with the
name of start point and end point and the code (or function) should
return the distance between the two.

I think the formula should be something like LOOKUP but I don't even
know where to start.

I'd be really grateful if someone could point me in the right
direction



A B C D
A * 5 8 9
B 5 * 3 4
C 8 3 * 7
D 9 4 7 *

Thanks in advance

Seamus


---
Message posted from http://www.ExcelForum.com/

Cecilkumara,

Excellent - many thanks for that. I'll now try to incorporate you
answer into my VBA code. I think I'll attempt to extract the "M3" an
"N3" values from controls on the form (maybe comboboxes )and work fro
there.

Thanks again

Seamu

--
Message posted from http://www.ExcelForum.com