VB only solving first row

[phras81]

I am new to all this and am looking for some help. I have a sheet with two columns of postcodes (column 1 and 3) and looking for each row to return the distance in column 5 and time in the 6. I have managed to get the code below working but it only returns the distance and time for the first row of the sheet. The code is below and if anyone can help I would be most greatful. I need it keep running down as far as there are rows with entries in column 1. Thank you in advance.

Please Login or Register  to view this content.

[ImStevenB]

Hi phras1,

Welcome to the Forum.

The first thing I noticed is the code is not setup to loop through the rows.

Try this:

Please Login or Register  to view this content.

What I did was add a couple of variables .. the X variable counts up the rows as it loops through .. the Rng variable is a range where I started it at A1.

What happens is it looks at the active cell in column 1 (column A) and if it is empty it will stop. If there is data there .. it goes through the code. the X variable represents the row number so when it is done, it moves to Row 2 and adds one to X making that a 2.

Hope this helps.

[phras81]

Hi phras1,

Welcome to the Forum.

The first thing I noticed is the code is not setup to loop through the rows.

Try this:

Please Login or Register  to view this content.

What I did was add a couple of variables .. the X variable counts up the rows as it loops through .. the Rng variable is a range where I started it at A1.

What happens is it looks at the active cell in column 1 (column A) and if it is empty it will stop. If there is data there .. it goes through the code. the X variable represents the row number so when it is done, it moves to Row 2 and adds one to X making that a 2.

Hope this helps.

Hi ImStevenB

Sorry for the delay on response. Thank you for your help with this. I have tried the code but I am getting an error 1004. When I debug it takes me to the line Do While Rng.Offset(, -17).Value <> ""

Can you help as I dont understand why it wouldnt work.

Thank you again for your help.

[ImStevenB]

Hi phras,

I see the line .. just take the Offset out of the line. Instead of it saying:

Please Login or Register  to view this content.

Make it

Please Login or Register  to view this content.

It was trying to check a value 17 columns to the left .. and of course there is not one that is to the left of Column A.

Hope this helps.

[phras81]

Thats great thank you. Can youo help with one other thing? It returns the number of miles in column 5 but it is followed by "mi" it means that I cannot sort the rows by closest to furthest away as 123 will come before 84 for example. Is there a way to just have it return the miles number with no text after?

I think it is to do with

Please Login or Register  to view this content.

Thank you again for your help

[ImStevenB]

Can you post a copy of your workbook? This way I can test it in your file.

[phras81]

Hi I have attached a copy of an example version. Thank you again for your help.