Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 833
  • Last Modified:

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.

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
0
investalert
Asked:
investalert
  • 8
  • 6
2 Solutions
 
SiddharthRoutCommented:
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
        '~~> Your code here
    Next i
End Sub

Open in new window


Sid
0
 
investalertAuthor 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
 
SiddharthRoutCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
borgunitCommented:
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

Open in new window

0
 
SiddharthRoutCommented:
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
 
investalertAuthor 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
 
SiddharthRoutCommented:
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

Open in new window


GPS-Coord-Example-1.xls
0
 
investalertAuthor 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
 
SiddharthRoutCommented:
And this is how the file looks once you populate it.

Sid
GPS-Coord-Example-1.xls
0
 
SiddharthRoutCommented:
Did it work investalert?

Sid
0
 
investalertAuthor 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
 
SiddharthRoutCommented:
>>>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
 
investalertAuthor 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
 
SiddharthRoutCommented:
Glad to be of help :)

Let us know if you get stuck :)


Sid
0
 
investalertAuthor 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now