I am new to Vba and I am trying to solve this longtitude and latitude problem. I am going to try and explain it to the best of my abilities. I have list of 20 stores with longs and lats and I have a list of 20 competitors with longs and lats. I'm trying to write a vba code that when ran, it will return how many of my stores is within 2 miles, 5 miles, and 10 miles and will also return how many of the competitors stores are within 2 miles, 5 miles, and 10 miles. Please see the attached image for further explanation..

Thanks in advance to whomever response!!!

Can't read the picture - resolution too small...


CE



Den 19.04.2012 22:13, Mastermind4real skrev:
I am new to Vba and I am trying to solve this longtitude and latitude
problem. I am going to try and explain it to the best of my abilities. I
have list of 20 stores with longs and lats and I have a list of 20
competitors with longs and lats. I'm trying to write a vba code that
when ran, it will return how many of my stores is within 2 miles, 5
miles, and 10 miles and will also return how many of the competitors
stores are within 2 miles, 5 miles, and 10 miles. Please see the
attached image for further explanation..

Thanks in advance to whomever response!!!


+-------------------------------------------------------------------+
|Filename: Capture.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=334|
+-------------------------------------------------------------------+

On 19/04/2012 21:13, Mastermind4real wrote:
I am new to Vba and I am trying to solve this longtitude and latitude
problem. I am going to try and explain it to the best of my abilities. I
have list of 20 stores with longs and lats and I have a list of 20
competitors with longs and lats. I'm trying to write a vba code that
when ran, it will return how many of my stores is within 2 miles, 5
miles, and 10 miles and will also return how many of the competitors
stores are within 2 miles, 5 miles, and 10 miles. Please see the
attached image for further explanation..

Thanks in advance to whomever response!!!

This sounds like computer science homework!
Show us your draft attempt and we might consider helping...

Hint: Step one is convert lat & long into (X,Y) distances in miles.

You could probably do it in a spreadsheet for a 20 vs 20 array.
(just requires a bit of lateral thinking)

+-------------------------------------------------------------------+
|Filename: Capture.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=334|
+-------------------------------------------------------------------+

Is there any newsgroup that *^&#Banter does not parasitise?

--
Regards,
Martin Brown

I don't know if it will help or not, but here is a UDF that I wrote
may years ago. The optional parameter determines what is returned
("D"istance or "H"eading). Be sure to watch out for "text wrap
around". You will need to pass the Lat/Lon as decimal values (ie: W
122-09-17 = -122.154853).
-pb

Function Calc_DistHdng(dblLa1 As Double, dblLo1 As Double, _
dblLa2 As Double, dblLo2 As Double, _
Optional strOpt As String) As Double

Dim dblDif As Double
Dim dblTmp As Double

Calc_DistHdng = 0

' validate data
If (dblLa1 < -90 Or dblLa1 90 Or dblLo1 < -180 Or dblLo1 180
Or _
dblLa2 < -90 Or dblLa2 90 Or dblLo2 < -180 Or dblLo2 180)
Then
Stop
Exit Function
End If
strOpt = UCase(strOpt)
If (strOpt < "H") Then
strOpt = "D"
End If

' compute the Longitude differance
dblDif = dblLo2 - dblLo1
If (dblDif 180) Then
dblDif = dblDif - 360
End If
If (dblDif < -180) Then
dblDif = 360 + dblDif
End If

' do the math here
If (strOpt = "D") Then ' Calculate Distance
dblTmp = Application.Acos(Cos(Application.Radians(90 -
dblLa1)) * _
Cos(Application.Radians(90 - dblLa2)) + _
Sin(Application.Radians(90 - dblLa1)) * _
Sin(Application.Radians(90 - dblLa2)) * _
Cos(Application.Radians(dblLo1 - dblLo2))) * 3440.065
Calc_DistHdng = dblTmp
Else ' Calculate Heading
dblTmp = Sin(Application.Radians(dblDif)) / _
((Cos(Application.Radians(dblLa1)) *
Tan(Application.Radians(dblLa2))) - _
(Sin(Application.Radians(dblLa1)) *
Cos(Application.Radians(dblDif))))

Calc_DistHdng = Application.Degrees(Atn(dblTmp))
If (dblDif = 0 And Application.Radians(dblLa1)
Application.Radians(dblLa2)) Then
Calc_DistHdng = 180
End If

If (dblDif < 0 And Calc_DistHdng < 0) Then
Calc_DistHdng = Calc_DistHdng + 360
Else
If ((dblDif < 0 And Calc_DistHdng 0) Or _
(dblDif 0 And Calc_DistHdng < 0)) Then
Calc_DistHdng = Calc_DistHdng + 180
End If
End If

End If

End Function