?
Solved

VBA code for uniform distance between coordinates

Posted on 2011-03-21
15
Medium Priority
?
828 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
[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
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 10

Accepted Solution

by:
borgunit earned 500 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
 

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 500 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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