The revolutionary project management tool is here! Plan visually with a single glance and make sure your projects get done.

Hi experts! I have an Excel spreadsheet with three columns and a large number of rows, a sample of which is shown below. I need to calculate new GPS coordinates a uniform distance between the known values in the table below. I also need to establish a waypoint name using the same scheme (I. E., A name that has a uniform increment for the name between the known values. I have written some VBA code but this one has me somewhat perplexed.

I need VBA code to fill in the blank cells between the coordinates in the table below. Note that the known values below can be greater than or less than the previous values. The intent is to calculate coordinates that are a uniform distance between the known values below. Likewise, the values between the known waypoints need to represent that uniformity.

Since there will always be four rows between the known values, I would like, for example, a routine that works with rows 2 through 7 to calculate the unknown values, and then move down and use the same routine to work with rows 7 through 12, etc.

Thank you in advance for your assistance!

A B C

1 Longitude Latitude Waypoint

2 -76.93283567 42.91389585 E100

3

4

5

6

7 -76.83104584 42.93277324 E105

8

9

10

11

12 -76.41578624 43.08443070 E130

13

14

15

16

17 -76.22519380 43.07906090 E140

I need VBA code to fill in the blank cells between the coordinates in the table below. Note that the known values below can be greater than or less than the previous values. The intent is to calculate coordinates that are a uniform distance between the known values below. Likewise, the values between the known waypoints need to represent that uniformity.

Since there will always be four rows between the known values, I would like, for example, a routine that works with rows 2 through 7 to calculate the unknown values, and then move down and use the same routine to work with rows 7 through 12, etc.

Thank you in advance for your assistance!

A B C

1 Longitude Latitude Waypoint

2 -76.93283567 42.91389585 E100

3

4

5

6

7 -76.83104584 42.93277324 E105

8

9

10

11

12 -76.41578624 43.08443070 E130

13

14

15

16

17 -76.22519380 43.07906090 E140

```
Sub Sample()
Dim i As Long, LastRow As Long
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow Step 5
'~~> Your code here
Next i
End Sub
```

Sid

Can you give me an example. Say can you hard code the values for Row 3-6 and then I will manage the rest?

Sid

Longitude Latitude Waypoint

-76.93283567 42.91389585 E100

-76.90738821 42.9186152 E101

-76.88194076 42.92333455 E102

-76.8564933 42.92805389 E103

-76.83104584 42.93277324 E104

-76.83104584 42.93277324 E105

Sid

GPS-Coord-Example.xls

Run the Macro Sample in Module 1

Sid

```
Sub Sample()
Dim i As Long, LastRow As Long
Dim ATemp As Double, BTemp As Double
With Sheets("A Route")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow - 5 Step 5
ATemp = (.Range("A" & i + 5).Value - .Range("A" & i).Value) / 5
.Range("A" & i).Offset(1).Value = .Range("A" & i).Value + ATemp
.Range("A" & i).Offset(2).Value = .Range("A" & i).Offset(1).Value + ATemp
.Range("A" & i).Offset(3).Value = .Range("A" & i).Offset(2).Value + ATemp
.Range("A" & i).Offset(4).Value = .Range("A" & i).Offset(3).Value + ATemp
BTemp = (.Range("B" & i + 5).Value - .Range("B" & i).Value) / 5
.Range("B" & i).Offset(1).Value = .Range("B" & i).Value + BTemp
.Range("B" & i).Offset(2).Value = .Range("B" & i).Offset(1).Value + BTemp
.Range("B" & i).Offset(3).Value = .Range("B" & i).Offset(2).Value + BTemp
.Range("B" & i).Offset(4).Value = .Range("B" & i).Offset(3).Value + BTemp
.Range("C" & i).AutoFill Destination:=.Range("C" & i & ":C" & i + 4), Type:=xlFillDefault
Next i
End With
End Sub
```

GPS-Coord-Example-1.xls

Is there a way to award the points to two solutions? Both you and borgunit were timely and responsive so I would like to award 250 points to each. How can I do that?

It doesn't matter as I have declared it as a double. Can you show me the realistic data with which you want to test?

>>>>Is there a way to award the points to two solutions? Both you and borgunit were timely and responsive so I would like to award 250 points to each. How can I do that?

Yes. Simply accept multiple solutions :)

Sid

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Open in new window