Solved

VBA code for uniform distance between coordinates

Posted on 2011-03-21
15
812 Views
Last Modified: 2012-05-11
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
Comment
Question by:investalert
  • 8
  • 6
15 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
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
        '~~> Your code here
    Next i
End Sub

Open in new window


Sid
0
 

Author Comment

by:investalert
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

by:SiddharthRout
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

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

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
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

by:investalert
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

by:SiddharthRout
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

Open in new window


GPS-Coord-Example-1.xls
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:investalert
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

by:SiddharthRout
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

by:SiddharthRout
ID: 35181537
Did it work investalert?

Sid
0
 

Author Comment

by:investalert
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

by:SiddharthRout
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

by:investalert
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

by:SiddharthRout
ID: 35181718
Glad to be of help :)

Let us know if you get stuck :)


Sid
0
 

Author Closing Comment

by:investalert
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now