Good morning
I am using the following code that I found on the web to calculate UK mileage which it does with reasonable accuracy (good enough for my use). However I am expecting and getting the result in cell E7 but it is running the query 7 times which makes it slow and the inserts ‘0’ in f7-k7, can anybody see why that would be?
Also my ultimate goal is to use something like Bing (preference) or Goggle Maps to search for one city to another city, for example Hamburg to Cologne or Paris to Lille or London to Manchester for example. Does anybody have some sample code that could do this?
Code:
Sub AutoShape2_Click()
counter = 6
beginrange = Worksheets("sheet1").Cells(counter, 4).Address
endrange = Worksheets("sheet1").Cells(counter, 14).Address
For Each c In Worksheets("Sheet1").Range(beginrange, endrange).Cells
If c.Offset(0, 1).Value = "" Then counter = counter + 2
If counter = 20 Then Exit Sub
beginrange = Worksheets("sheet1").Cells(counter, 4).Address
endrange = Worksheets("sheet1").Cells(counter, 14).Address
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
URL = "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp"
IE.Navigate2 URL
Do While IE.readyState 4
DoEvents
Loop
Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)
Set Postcodebox = inputform.Item(0)
Postcodebox.Value = c.Value
Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = c.Offset(0, 1).Value
Set POSTCODEbutton = inputform.Item(2)
POSTCODEbutton.Click
Do While IE.busy = True
Loop
Set Table = IE.document.getElementsByTagname("Table")
Set DistanceTable = Table.Item(3)
Set DistanceRow = DistanceTable.Rows(2)
distance = Val(Trim(DistanceRow.Cells(2).innertext))
c.Offset(1, 1).Value = distance
IE.Quit
Next
End Sub
