sub for calculating distance via post code / variable range.

[Atishoo]

Hi im using the sub below to calculate a mileage sheet populated with post
codes.
I am using the variable "counter" to set the row that the sub applies to.
So in teory it should calculate the distances for each post code in row 6
(counter initial value) and when it comes to an empty cell in row 6 (if
c.value offset (0, 1)="" then counter = counter +2) it should move onto row 8
and so on until hitting row 20 (if counter = 20 exit sub).

But Im missing something when it hits an empty cell it just keeps going on
row 6!
What am i doing wrong here??




Private Sub CommandButton1_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


many thanks

 

[Don Guillett]

Hard to test without seeing the project.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Rick Rothstein]

You need to post your code if you expect someone to help you figure out why
the code is not working as you expect.

 

[Rick Rothstein]

Ignore my comment... your 4 line feeds in front of the code made it so that
the code was below the bottom of the display area for the message (and I
didn't think to scroll the screen).

 

[Bob Phillips]

By the time that you up counter and use that to redefine beginrange and
endrange, you are already in the cell processing loop, so it makes no
difference.

Just change the code to do nothing if that cell is empty and pass onto the
next cell in the specified range.

 

[Peter T]

The UK postcode distance URL code works fine, it looks to be same as the
example posted by Joel recently. Before you even look at that I suggest you
work out of logic of getting your pairs of postcodes. In your test loop dump
them to the immediate window, in the loop

Debug.Print BeginCode, EndCode
(ctrl-g to view the immediate window)

If you still have problems post back details of where your data is and your
code (without any URL stuff which is a different matter and only confuses)

If you work it out by yourself adpat into the IE/URL code, a couple of tips
Don't create and destroy a new IE instance in each loop

before the loop
Set IE = CreateObject("InternetExplorer.Application")
after the loop
IE Quit ' assuming you don't want to see the IE

distance = Val(Trim(DistanceRow.Cells(2).innertext))

the site returns 4 distance values in respective cells
Distance as crow flies: Cells(2) Km, 3 miles
Distance by road: 4 Km, 5 miles

Also note the site only works with the first part of the postcode, so don't
expect absolute accurancy.

Regards,
Peter T

I suggest you work out your logic to return your pairs of postcodes before
you start

 

[Atishoo]

Yes joel was looking at the sub for me as I was having probs with getting the
mileage data back from the web site!
Bob Philips was absolutely right about just not doing anything if the cell
is empty (or rather only doing something if the cell is full ie if c.value
<>"" then)
Have stopped killing IE for each calculation as sugested and set the range
as a named range across the area of worksheet 1 in which I input postcodes.
All works well now sub as follows:

Private Sub CommandButton1_Click()

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

For Each c In Worksheets("Sheet1").Range("pcrange").Cells
If c.Offset(0, 1).Value <> "" Then

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

End If
Next
IE.Quit

End Sub

thanks to all

 

[Atishoo]

Oh yes Also set the ie.visible to false so the mileage just magically
appears! Great!