| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sub for calculating distance via post code / variable range.
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sub for calculating distance via post code / variable range.
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 (MVP - Excel) "Atishoo" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sub for calculating distance via post code / variable range.
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). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Atishoo" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sub for calculating distance via post code / variable range.
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. -- __________________________________ HTH Bob "Atishoo" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sub for calculating distance via post code / variable range.
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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sub for calculating distance via post code / variable range.
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 "Peter T" wrote: 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sub for calculating distance via post code / variable range.
Oh yes Also set the ie.visible to false so the mileage just magically appears! Great! "Atishoo" wrote: 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 "Peter T" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Time from Speed and Distance? | Excel Worksheet Functions | |||
| sub to get post code to post code mileage from web site | Excel Programming | |||
| Calculating the distance customers live from a branch office | Excel Worksheet Functions | |||
| Calculating Average using a Variable Range | Excel Programming | |||
| VBA Code to name a variable range | Excel Programming | |||
Linear Mode
