I am trying to find out the distance from one zip code to 600 other zip codes. I like to know if there is a simple code for it instead of typing 600 lines of code? Below is the code I have, as you see the cell increases by one and as I said this goes up to 600. Can one code do them all instead of repeating for 600 times?
Set oApp = CreateObject("MapPoint.Application.NA.16")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute

Thanks for responding but I get an errror at the last line of your code. Am I doing anything wrong here?

davidb

08-16-2009, 04:19 AM

The symbol for quotation marks in the last line isn't right. Replace them by the normal keyboard symbol.

davidb

08-16-2009, 04:22 AM

Also the line to ouput the result should read

Worksheets("Sheet1").Cells(NRow, 2) = objRoute.Distance

shahdelsol

08-16-2009, 05:37 AM

Also the line to ouput the result should read

Worksheets("Sheet1").Cells(NRow, 2) = objRoute.Distance
Yes David, I did change quotation mark but still the code stops at last line. Any Idea?

davidb

08-16-2009, 06:53 AM

what's the error message and what's the format of your input data?

shahdelsol

08-16-2009, 06:57 AM

what's the error message and what's the format of your input data?
The code stops at last line and doesnot continue and mappoint only calculate the first two zips and stay open with debug and end dialog box. something missing at last line I think and the format is .xlsm

Please note that there is no error handling built into the code. so in particular all your zip codes have to be valid or the program will fail. Also based on your first post I've assumed that the first cell of column A contains your first 'origin' zip and that all the other zips are in the same column without any blank cells. The program ends when it finds a blank cell in that column.

Please note that there is no error handling built into the code. so in particular all your zip codes have to be valid or the program will fail. Also based on your first post I've assumed that the first cell of column A contains your first 'origin' zip and that all the other zips are in the same column without any blank cells. The program ends when it finds a blank cell in that column.
Thanks so much it is working now. If you look at your original code there was one line different and that was the problem. now I have one more question if you don't mind: If I want to change my primary zip cell to (1,3) and second zip to (3,1) and result to (3,3) and rest of zip underneath (3,1) just like before, how would be new code look like?

PROFIT100 Consulting

07-27-2010, 02:15 AM

Distances between Postcodes, Cities, Adresses you can calculate very easily by using the MapPoint Tool "IC-DistanceCalc (http://www.mappoint2010.info/html/ic-tools_download.html)", (Excel AddIn).
You can download the tool for free. :flowers:
If you register (http://seminarzentrum-darmstadt.de/tinc?key=4aifjtsv&formname=USA), we will send you a 1 year upgrade to PLUS-Version (unlimited) for free plus an Excel-sheet with all USA-Postcodes with Longitudes ans Altitudes.