1. 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

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

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.postcode.org.uk/country/uk/_postcode-distance-calculator.asp"
IE.Navigate2 URL
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```

2. You're trying to calculate a single distance, so there is no need for a loop in the code. See attached version.

[attachment=86525:Code.txt]

3. Originally Posted by HansV
You're trying to calculate a single distance, so there is no need for a loop in the code. See attached version.

[attachment=86525:Code.txt]
Fantastic, Thanks Hans

It makes it so much quicker it does not even show the web site opening now

4. Originally Posted by stevehocking
It makes it so much quicker it does not even show the web site opening now
I removed the line that made the browser visible; it wasn't needed.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•