UPDATE: OK, I've gotten this far.
I'm able to get it to calculate the total route mileage based on each individual point on the route then I'm using Excel to calculate the individual segment mileage. From there, Excel can calculate the number of hours (rounded up) for that segment. Then, inserting a start time SHOULD give me my scheduling information. What I'm missing is some process that looks for a null value in the excel spreadsheet zip code column and STOPS the process there.
Please see my totally inept attempt at showing what I mean in the code below. Note that I've rem'd those lines out. But it should tell you essentially what I want it to do. Any suggestions on how to phrase that so my computer knows what I'm talking about? I know next to nothing about vb scripting so I'm hoping someone might know.
Here's what I want it to do... sort of: If Worksheets("Sheet1").Cells(4, 1) = "" Then End Sub
Any help would be appreciated.
Code:
Private Sub CommandButton1_Click()
Set oApp = CreateObject("MapPoint.Application.NA.13")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute
szZip1 = Worksheets("Sheet1").Cells(2, 1)
szZip2 = Worksheets("Sheet1").Cells(3, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(3, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(4, 1) = "" Then End Sub
szZip3 = Worksheets("Sheet1").Cells(4, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip3).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(4, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(5, 1) = "" Then End Sub
szZip4 = Worksheets("Sheet1").Cells(5, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip4).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(5, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(6, 1) = "" Then End Sub
szZip5 = Worksheets("Sheet1").Cells(6, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip5).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(6, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(7, 1) = "" Then End Sub
szZip6 = Worksheets("Sheet1").Cells(7, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip6).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(7, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(8, 1) = "" Then End Sub
szZip7 = Worksheets("Sheet1").Cells(8, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip7).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(8, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(9, 1) = "" Then End Sub
szZip8 = Worksheets("Sheet1").Cells(9, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip8).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(9, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(10, 1) = "" Then End Sub
szZip9 = Worksheets("Sheet1").Cells(10, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip9).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(10, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(11, 1) = "" Then End Sub
szZip10 = Worksheets("Sheet1").Cells(11, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip10).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(11, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(12, 1) = "" Then End Sub
szZip11 = Worksheets("Sheet1").Cells(12, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip11).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(12, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(13, 1) = "" Then End Sub
szZip12 = Worksheets("Sheet1").Cells(13, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip12).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(13, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(14, 1) = "" Then End Sub
szZip13 = Worksheets("Sheet1").Cells(14, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip13).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(14, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(15, 1) = "" Then End Sub
szZip14 = Worksheets("Sheet1").Cells(15, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip14).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(15, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(16, 1) = "" Then End Sub
szZip15 = Worksheets("Sheet1").Cells(16, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip15).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(16, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(17, 1) = "" Then End Sub
szZip16 = Worksheets("Sheet1").Cells(17, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip16).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(17, 3) = objRoute.Distance
'If Worksheets("Sheet1").Cells(18, 1) = "" Then End Sub
szZip17 = Worksheets("Sheet1").Cells(18, 1)
objRoute.Waypoints.Add objMap.FindResults(szZip17).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(18, 3) = objRoute.Distance
End Sub
TIA
- co