I am using the following code for calculating distance between the zips. It works great but if there is bad zipcode or new zipcode in one cell that mappoint cannot find, the mappoint and macro stops with this error: " Run-time error- 22.... the requested member of the collection doesnot exist. Use a valid name or index number" and then it gives the option of: End or Debug which is not helpful. I want a macro instead to do this for me: if there is wrong zipcode, an pop up would give me an option to correct zip code in which cell or ignore and continue to calculate the rest of zip codes and this is the code I am using:
Code:Private Sub CommandButton1_Click() Set oApp = CreateObject("MapPoint.Application.NA.16") oApp.Visible = True Set objMap = oApp.NewMap Set objRoute = objMap.ActiveRoute szZip1 = Worksheets("Sheet1").Cells(1, 3) NRow = 3 Do szZip2 = Worksheets("Sheet1").Cells(NRow, 1) 'Add route stops and calculate the route objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1) objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1) objRoute.Calculate Worksheets("Sheet1").Cells(NRow, 3) = objRoute.Distance objRoute.Clear NRow = NRow + 1 Loop While Worksheets("Sheet1").Cells(NRow, 1) <> "" objMap.Saved = True End Sub