How about this?
Create a userform with 2 comboboxes on it (combobox1 and combobox2).
Add a label (label1) (LABEL1 <-- Final character is one)
Then put this behind the userform:
Option Explicit
Dim FirstCityRng As Range
Dim SecondCityRng As Range
Dim MileageRng As Range
Private Sub ComboBox1_Change()
Dim myCell As Range
Me.Label1.Caption = "Select Two Cities"
Me.ComboBox2.Clear
If Me.ComboBox1.Value = "" Then
Exit Sub
End If
For Each myCell In FirstCityRng.Cells
If myCell.Value = Me.ComboBox1.Value Then
Me.ComboBox2.AddItem myCell.Offset(0, 1).Value
End If
Next myCell
End Sub
Private Sub ComboBox2_Change()
Dim res As Variant
Dim myFormula As String
Me.Label1.Caption = "Select Two Cities"
If Me.ComboBox1.Value = "" _
Or Me.ComboBox2.Value = "" Then
Exit Sub
End If
myFormula = "=Match(1, (" & FirstCityRng.Address(external:=True) _
& "=""" & Me.ComboBox1.Value & """)" _
& "*(" & SecondCityRng.Address(external:=True) _
& "=""" & Me.ComboBox2.Value & """),0)"
res = Application.Evaluate(myFormula)
If IsError(res) Then
'shouldn't happen!
MsgBox "Design error!"
Else
Me.Label1.Caption = "Total Mileage = " & MileageRng(res)
End If
End Sub
Private Sub UserForm_Initialize()
Dim tableWks As Worksheet
Dim myCell As Range
Me.Label1.Caption = "Select Two Cities"
Set tableWks = Worksheets("table")
Me.ComboBox1.Style = fmStyleDropDownList
Me.ComboBox2.Style = fmStyleDropDownList
With tableWks
If .FilterMode Then
.ShowAllData
End If
Set FirstCityRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
Set SecondCityRng = FirstCityRng.Offset(0, 1)
Set MileageRng = FirstCityRng.Offset(0, 2)
FirstCityRng.AdvancedFilter Action:=xlFilterInPlace, unique:=True
With FirstCityRng
For Each myCell In .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.Cells.SpecialCells(xlCellTypeVisible)
With Me.ComboBox1
.AddItem myCell.Value
End With
Next myCell
End With
.ShowAllData
End With
End Sub
Then create a worksheet Named Table.
Put your values in A2:C2000 (headers in row 1 are required).
And test it out.
