Solved

# VBA code for uniform distance between coordinates

Posted on 2011-03-21
822 Views
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
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 6

LVL 30

Expert Comment

ID: 35180815
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 Comment

ID: 35181068
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

LVL 30

Expert Comment

ID: 35181103
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

LVL 10

Accepted Solution

borgunit earned 125 total points
ID: 35181169
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

LVL 30

Expert Comment

ID: 35181172
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 Comment

ID: 35181232
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

LVL 30

Expert Comment

ID: 35181381
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 Comment

ID: 35181393
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

LVL 30

Assisted Solution

SiddharthRout earned 125 total points
ID: 35181397
And this is how the file looks once you populate it.

Sid
GPS-Coord-Example-1.xls
0

LVL 30

Expert Comment

ID: 35181537

Sid
0

Author Comment

ID: 35181597
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

LVL 30

Expert Comment

ID: 35181618
>>>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 Comment

ID: 35181708
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

LVL 30

Expert Comment

ID: 35181718
Glad to be of help :)

Let us know if you get stuck :)

Sid
0

Author Closing Comment

ID: 35181790
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month8 days, 7 hours left to enroll