Calculate the distance between 2 postcodes in miles

[Jaffabfc]

Hi,

i am wanting to be able to calculate the distance between 2 postcodes in excel.

i have a list of around 3000 places in the UK and i am wanting to know which is the closest to were i currently am.

To do this i am wanting to be able to put a column on the end (e.g. column f) to work out how much it is from A to B, is this even possible?

I am hoping there is something simple that i can do even if it means linking google maps into it.

Thanks

 

[John_w]

I've added the Microsoft XML v6.0 reference in VBA but i'm getting #NAME? when I try to use =GetDistance("T2P 4R5", "M9N 1K9")

Look at my post two posts above yours for possible causes of the error and you don't need the reference.

 

[carm]

Look at my post two posts above yours for possible causes of the error and you don't need the reference.

Thank you for your help

I've got it working now. One last question, is their anyway to manual run the lookup? i.e if I make changes to the postcodes to reference it doesn't perform a lookup

 

[John_w]

anyway to manual run the lookup? i.e if I make changes to the postcodes to reference it doesn't perform a lookup

What do you mean by manually run the lookup?

If you reference cells containing the start and end postcodes in the formula, instead of hardcoding them, then it will recalculate the formula when you change any of the postcodes, for example:

=GetDistance(A1,B1)

 

[carm]

@John_w Thanks for your help with this script. Its working great.


Is their any way I change things so once it has looked up the distance it doesn't do it again? My logistics spreadsheet at any time has around 200 postcode locations so when I open the sheet up it resolves every postcode which takes ages

Thanks

 

[John_w]

Is their any way I change things so once it has looked up the distance it doesn't do it again? My logistics spreadsheet at any time has around 200 postcode locations so when I open the sheet up it resolves every postcode which takes ages

Delete the =GetDistance cell formulas and instead run this macro, which loops through the rows in the active sheet starting at A2 and reads the start postcode from column A and the end postcode from column B and puts the distance in miles in column C.

Public Sub Calculate_Distances()

    Dim cell As Range
    
    With ActiveSheet
        For Each cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            cell.Offset(, 2).Value = GetDistance(cell.Value, cell.Offset(, 1).Value)
        Next
    End With
    
End Sub

 

[carm]

Delete the =GetDistance cell formulas and instead run this macro, which loops through the rows in the active sheet starting at A2 and reads the start postcode from column A and the end postcode from column B and puts the distance in miles in column C.

Public Sub Calculate_Distances()

    Dim cell As Range
   
    With ActiveSheet
        For Each cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            cell.Offset(, 2).Value = GetDistance(cell.Value, cell.Offset(, 1).Value)
        Next
    End With
   
End Sub

That's great, can you explain which part of this code I would need to alter so I can hard code the end postcode (Currently column B)?

 

[John_w]

That's great, can you explain which part of this code I would need to alter so I can hard code the end postcode (Currently column B)?

The 2nd argument to GetDistance is the end postcode, therefore:

           cell.Offset(, 2).Value = GetDistance(cell.Value, "end postcode")