Ross
asked on
adapt excel postcode distance calculator
I'm trying to adapt code I've found to my needs and it's working as I'd expect pretty much. However, there's an issue I need to fix I hope you guys can help with.
Basically, I have engineers travelling from one place to another, frequently via a middle destination, but not always. I have a script that looks up the postcode distances and this works just fine for valid postcodes, but I can't work out how to put logic in the code to make it work for when the engineer travels without going to the middle destination.
To explain, look at engineer1 screenshot.
As you can see, engineers normally go from home to tote, to job. However, they MAY go direct from tote to job, or direct from home to job. I need the code to have enough logic to compute this somehow.
The code I'm using currently does the "home to tote" column calculation, and then does the "tote to job" calculation, and this works well. However obviously the "n/a"'s cause a problem and reflect 0 miles.
I'm hoping someone can assist me :)
Thanks!
My current code is attached.
Ross
Basically, I have engineers travelling from one place to another, frequently via a middle destination, but not always. I have a script that looks up the postcode distances and this works just fine for valid postcodes, but I can't work out how to put logic in the code to make it work for when the engineer travels without going to the middle destination.
To explain, look at engineer1 screenshot.
As you can see, engineers normally go from home to tote, to job. However, they MAY go direct from tote to job, or direct from home to job. I need the code to have enough logic to compute this somehow.
The code I'm using currently does the "home to tote" column calculation, and then does the "tote to job" calculation, and this works well. However obviously the "n/a"'s cause a problem and reflect 0 miles.
I'm hoping someone can assist me :)
Thanks!
My current code is attached.
Ross
Private Sub GetDistances_Click()
RowCount = 2
FirstCol = "C"
LastCol = "N"
ColCount = Columns(FirstCol).Column
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
URL = "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp"
IE.Navigate2 URL
Do While IE.readyState <> 4 Or _
IE.busy = True
DoEvents
Loop
With Worksheets("Sheet1")
Do While .Cells(RowCount, ColCount) <> ""
StartLocation = .Cells(RowCount, ColCount).Value
EndLocation = .Cells(RowCount, ColCount + 1).Value
Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)
Set Postcodebox = inputform.Item(0)
Postcodebox.Value = StartLocation
Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = EndLocation
Set POSTCODEbutton = inputform.Item(2)
POSTCODEbutton.Click
Do While IE.readyState <> 4 Or _
IE.busy = True
DoEvents
Loop
Set Table = IE.document.getElementsByTagname("Table")
Set DistanceTable = Table.Item(3)
Set DistanceRow = DistanceTable.Rows(2)
distance = Val(Trim(DistanceRow.Cells(5).innertext))
Cells(RowCount + 0, ColCount + 7) = distance
RowCount = RowCount + 1
Loop
End With
RowCount = 2
FirstCol = "D"
LastCol = "N"
ColCount = Columns(FirstCol).Column
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
URL = "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp"
IE.Navigate2 URL
Do While IE.readyState <> 4 Or _
IE.busy = True
DoEvents
Loop
With Worksheets("Sheet1")
Do While .Cells(RowCount, ColCount) <> ""
StartLocation = .Cells(RowCount, ColCount).Value
EndLocation = .Cells(RowCount, ColCount + 1).Value
Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)
Set Postcodebox = inputform.Item(0)
Postcodebox.Value = StartLocation
Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = EndLocation
Set POSTCODEbutton = inputform.Item(2)
POSTCODEbutton.Click
Do While IE.readyState <> 4 Or _
IE.busy = True
DoEvents
Loop
Set Table = IE.document.getElementsByTagname("Table")
Set DistanceTable = Table.Item(3)
Set DistanceRow = DistanceTable.Rows(2)
distance = Val(Trim(DistanceRow.Cells(5).innertext))
Cells(RowCount + 0, ColCount + 7) = distance
RowCount = RowCount + 1
'ColCount = ColCount + 1
Loop
End With
IE.Quit
End Sub
engineer1.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually not sure on reflection whether that change will do the trick. Could you post a small workbook please?
ASKER
Absolutely spot on, and exactly what I was looking for. Solved the issue promptly and accurately. thank you very much - saved me a LOT of head scratching!
Ok, glad it worked! A pleasant surprise.