There's no need for VBA. Your problem can be solved using just Excel. Also, the reason you may be having problems with orientation is that you have your longitudes as positive values. By convention, western hemisphere longitudes are negative values.
Here's how to do it w/o VBA:
Suppose your zip code database is in H4:J{n} where H is the zip code, I the longitude, and J the latitude. Also, none of the cells H1:H3 contain any other data.
Create a named formula (Insert | Name > Define...)
ZipCodeList =OFFSET(Sheet1!$H$5,0,0,COUNTA(Sheet1!$H:$H)-1,1)
The radius of the Earth is in J1. Name that cell _R.
In H4:O4 enter the names:
Zip Code
Longitude
Latitude
Distance from ZC of interest
Closest distances
Plot Long
Plot Lat
Plot Label
Select K5 and then create the names:
_Lat1 =Sheet1!$D$5
_Lat2 =Sheet1!$J5
_Lon1 =Sheet1!$C$5
_Lon2 =Sheet1!$I5
It is important to select K5 before creating the above names since _Lat2 and _Lon2 use a *relative* row reference.
B5 will contain the zip code of interest. To make your worksheet easier to use data validation for that cell restricting entries to the named list ZipCodeList.
C5 will contain the longitude of the zip code of interest. Enter the formula =VLOOKUP(B5,OFFSET(ZipCodeList,0,0,,3),2,FALSE)
D5 will contain the latitude of the zip code of interest. Enter the formula =VLOOKUP(B5,OFFSET(ZipCodeList,0,0,,3),3,FALSE)
In E5 enter the formula =B5&CHAR(13)&" " This will be the data label on the chart to indicate the zip code of interest.
C6 will contain the number of zip codes nearest to the one of interest we want to see.
Next, in K5 enter the formula for the distance of this zip code from the one of interest =IF($H5=$B$5,0,ACOS(SIN(_Lat1)*SIN(_Lat2)+COS(_Lat1)*COS(_Lat2)*COS(_Lon2-_Lon1))*_R)
In L5 enter the array formula =IF(SUM(--(SMALL(OFFSET(ZipCodeList,0,3),ROW(INDIRECT("2:"&($C$6+1))))=K5)),K5,NA())
To complete an array formula do *not* use the ENTER key. Instead use the combination of CTRL+SHIFT+ENTER. If done correctly, Excel will show the formula enclosed in curly brackets { and }
In M5 enter =IF(ISNA($L5),NA(),I5)
In N5 enter =IF(ISNA($L5),NA(),J5)
In O5 enter =H5&CHAR(13)&" ("&ROUND(K5,0)&")"
Copy K5:O5 as far down as you have data in H:J.
Plot M:N in a XY Scatter chart. Also plot C5:D5 on the same chart as a separate series. Use O as the data label for the 1st series and E5 for the 2nd. Format the 2 series to taste.
To add the data labels either use an utility like Rob Bovey's XY Chartlabeler (
www.appspro.com) or do it by hand along the lines of
Data Labels
http://www.tushar-mehta.com/excel/newsgroups/data_labels/index.html
Frankly, Rob's utility will be easier to use.
I developed the above using Excel 2007. The resulting file is at
http://www.box.net/shared/zhsih5z008 The same file, saved in the 97-2003 format, is at
http://www.box.net/shared/rgxar3n6ft
The box.net service is on a as-is basis. I have no control over it and no idea of how long it will retain the above files.
I've got it all but figured out now. The Chart will not have connecting lines, but I have been able to create as "Scatter Chart" and using VBA {snip}
But now I have a slight Arrangement problem that I'm having trouble figuring out.
{snip}
-Robert
