VBA Distance between two points (Lat Lon)

[purceld2]

I need to calculate the distance between two address. I have the LAT and LON on all of the addresses... there are tens of thousands of address. I need to do compares

I have found this Excel Formula...not sure how it work though

=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Lon1-Lon2))) *6371

Any ideas

 

[jolivanes]

@ blongmire
This thread is just about a year and a half old.
Please start a new thread and refer to this one if you feel it is relevant.
Also, put your code between code tags.

 

[blongmire]

I have one row per city. Each city has a lat lon, and I'm trying to fill a value into column G for that cities distance to Dallas. I'm storing Dallas' lat in cell L2 and Dallas' lon in cell m2.

Once I run the distance to dallas, I'll update cells L2 and M2 to be Houston's lat lon and run the macro pasting values into column H.

Here's a link to the file.

https://app.box.com/s/cyht1xinna7ygpc4zqvv

Thank you for the help and quick reply!

Bob

 

[blongmire]

I have one row per city. Each city has a lat lon, and I'm trying to fill a value into column G for that cities distance to Dallas. I'm storing Dallas' lat in cell L2 and Dallas' lon in cell m2.

Once I run the distance to dallas, I'll update cells L2 and M2 to be Houston's lat lon and run the macro pasting values into column H.

Here's a link to the file.

https://app.box.com/s/cyht1xinna7ygpc4zqvv

Thank you for the help and quick reply!

Bob

 

[shg]

How about just using the UDF and a formula?

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​						​	DFW​	HOU​	AUS​	SAT​	ELP​
2​					Lat​	​	32.8500​	29.6500​	30.2000​	29.5333​	31.8000​
3​	City Name​	Zip​	2010 Census​	Med Inc​	​	Lon​	-96.8500​	-95.2833​	-97.6667​	-98.4667​	-106.3833​
4​	Abbott	76621​	356​	43500​	31.8845​	-97.0772​	68.01​	187.55​	121.51​	182.21​	546.09​
5​	Abernathy	79311​	2805​	32393​	33.9494​	-101.8178​	296.42​	485.07​	355.14​	363.08​	303.67​
6​	Abilene	79601​	117063​	26307​	32.3445​	-99.8021​	175.35​	325.96​	194.56​	209.73​	387.09​
7​	Ace	77326​	0​	22500​	30.5209​	-94.8221​	200.27​	66.19​	171.03​	228.43​	688.90​
8​	Ackerly	79713​	220​	32222​	32.5273​	-101.7160​	283.81​	429.29​	287.94​	282.46​	277.55​
9​	Addison	75001​	13056​	57064​	32.7673​	-96.7776​	7.10​	232.77​	184.95​	244.74​	564.86​
10​	Adrian	79001​	166​	35417​	35.4053​	-102.6027​	373.31​	582.69​	459.76​	471.82​	330.67​

In G4 and copy down and right,

=3958.75587 * CentralAngle(G$2, G$3, $E4, $F4)

 

[blongmire]

YES! That is much easier!

Thank You!

I really appreciate the help!

 

[shg]

You're welcome.