# VBA code for uniform distance between coordinates

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.

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
###### Who is Participating?

Commented:
This will do it but it does not account for a myriad of variances
``````ublic Sub Rows_FillMissingValsCoordinate()

'------------------------------------------------------------------------------
'
'
'------------------------------------------------------------------------------
Dim lLastRow As Long
Dim l As Long
Dim m As Long
Dim vInfo As Variant
Dim dVariance As Double
Dim dLong As Double
Dim dLat As Double
Dim lWay As Long
'Dim n As Integer
'Dim p As Integer
'''''''''''''''''''''''''''''''''''''''

lLastRow = LastXlCell(ActiveSheet).Row 'find last row
vInfo = ActiveSheet.Range("A1", "C" & CStr(lLastRow)) 'get range of info

For l = 2 To UBound(vInfo, 1) - 5 'start at row 2 since top is header
If Len(vInfo(l, 1)) > 0 Then
dLong = (vInfo(l, 1) - vInfo(l + 5, 1)) / 5
dLat = (vInfo(l, 2) - vInfo(l + 5, 2)) / 5
lWay = CLng(Mid\$(vInfo(l, 3), 2))
For m = 1 To 4
ActiveSheet.Cells(l + m, 1) = vInfo(l, 1) - (m * dLong) 'long
ActiveSheet.Cells(l + m, 2) = vInfo(l, 2) - (m * dLat) 'lat
ActiveSheet.Cells(l + m, 3) = "E" & lWay + m
Next m
l = l + 4
End If
Next l

End Sub
Public Function LastXlCell(ByRef xlWs As Worksheet) As Range
'------------------------------------------------------------------------------
'
'
'
'           Error-handling is in case there is not any data in the worksheet
'------------------------------------------------------------------------------
Dim LastRow&, LastCol%
'''''''''''''''''''''''''''''''''''''''
On Error Resume Next

With xlWs
' Find the last real row
LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
' Find the last real column
LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
' Finally, initialize a Range object variable for
' the last populated row.
Set LastXlCell = xlWs.Cells(LastRow&, LastCol%)

End Function
``````
0

Commented:
To loop through rows 2, 7, 12 etc you can use

``````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
Next i
End Sub
``````

Sid
0

Author Commented:
Hi SiddharthRout, I appreciate your response, but your routine does not appear to address the problem. The code needs to calculate the missing values (rows 3 - 6) between the known values (rows 3 and 7). Then move down and calculate the missing values (rows 8 - 11) between the known values (rows 7 and 12), etc. to the end of rows in the worksheet. Hope this helps.
0

Commented:
investalert: Yes, you are right and that is because I don't how do you want the cells in rows 3-6 to be filled up. Do you have any specific calculation? That code will go in the part which says "'~~> Your code here" in the code that I gave above.

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
0

Commented:
Just guessing. Is this the result that you want form Row 2-7?

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
0

Author Commented:
OK Sid, I am attaching a 2003 Excel spreadsheet that has the information you are looking for. The calculated values for the blanc cells are shown in columns H and I with the formulas located there also. These are the values I need in their associated rows in columns A and B. Please translate the formulas to VBA code. You will also see the extent of rows that need to be calculated at row 217. Thanks!
GPS-Coord-Example.xls
0

Commented:
Is this what you want?

Run the Macro Sample in Module 1

Sid

Code Used

``````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
0

Author Commented:
Hi borgunit, your code works to fill the coordinates. However, some waypoints are more than 5 numbers apart (e.g., Waypoint E105 to E130) so the increment would be 5 instead of 1. Please modify your code as necessary and I will accept the solution and award you the points. Thanks!
0

Commented:
And this is how the file looks once you populate it.

Sid
GPS-Coord-Example-1.xls
0

Commented:

Sid
0

Author Commented:
Thanks SiddharthRout, your code, although different, also solves the problem. However, the code needs to be modified to show the increment to be 5 or something else between A025 and A055. This one is difficult because the difference divided by 5 is not a whole number. Anyway, if you can modify it by using a increment of 5, that would be great.

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?
0

Commented:
>>>This one is difficult because the difference divided by 5 is not a whole number.

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
0

Author Commented:
Actually, after closer examination of the code, I can follow the logic and modify as necessary to take care of the waypoint issue. I may have to rename the known points and then let the code increment accordingly. Thanks so much each of you and I will accept multiple solutions and award points.
0

Commented:
Glad to be of help :)

Let us know if you get stuck :)

Sid
0

Author Commented:
Both solutions solved the problem using different code routines. The coordinate calculations were complete but the code to name waypoints needs to be modified to account ot variations in difference between increment values. Good job!
0
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.