First let me explain the big picture. I'm trying to make a function to look
at list of zipcodes. They are listed in this format "Augusta GA" "298,
308-310". City name in one cell and the zips in another. I want the function
to look at these entries and tell me if a certain zipcode is in the list.
For example if I enter a zip of 309 it should return Augusta GA.
Now, I'm at the point where I have made a function to fill the range into an
array. The functions leaves me with an array of (308, 309, 310).
I have two questions. First of all is the best way to approach this?
Second if so, what do I need to know about passing arrays from one function
to another.

Thanks
Mike

Mike,

Here is an example of what I think you are doing

Function ZipWhere(Zip)
Dim aryZips, aryAreas
Dim iPos As Long

ZipCodes aryZips, aryAreas
On Error Resume Next
iPos = Application.Match(Zip, aryZips, 0)
On Error GoTo 0
If iPos = 0 Then
ZipWhere = CVErr(xlErrNA)
Else
ZipWhere = aryAreas(iPos)
End If

End Function

Function ZipCodes(ByRef pZipCodes, ByRef pZipAreas)

'this is a simulation to load the array
pZipCodes = [{308,401,517}]
pZipAreas = [{"Atlanta GA","Austin TX","Birmingham AL"}]
'your code would be different here, but it
'would load the same arrays
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mikebres" wrote in message
...
First let me explain the big picture. I'm trying to make a function to
look
at list of zipcodes. They are listed in this format "Augusta GA" "298,
308-310". City name in one cell and the zips in another. I want the
function
to look at these entries and tell me if a certain zipcode is in the list.
For example if I enter a zip of 309 it should return Augusta GA.
Now, I'm at the point where I have made a function to fill the range into
an
array. The functions leaves me with an array of (308, 309, 310).
I have two questions. First of all is the best way to approach this?
Second if so, what do I need to know about passing arrays from one
function
to another.

Thanks
Mike

I see some interesting ideas in your code. But the starting point is a
little different. Instead of a list of arrays, its a list of string values
from a spreadsheet. Here is a small section of the list I am working with.
This is a list on an Excel spreadsheet:
AADC AUGUSTA GA 308 298, 308-309
AADC MACON GA 310 310, 312, 316-319, 398
AADC JACKSONVILLE FL 320 299, 304, 313-315, 320-323, 326, 344
AADC PENSACOLA FL 325 324, 325

Here is the function so far:
Function ZipRange(TString As String) As Boolean
Dim Hyph As Integer, cWord As Integer, i As Integer, arrSize As Integer
Dim tWord As String, cHyph As Integer, j As Integer
Dim tArray() As String, fArray() As String

'Check to see if there is range of numbers
Hyph = InStr(TString, "-")
If Hyph 0 Then
'Seperate the two ends of the range and fill in the middle
cHyph = CountCSWords(TString, "-")
If cHyph = 2 Then
tStart = (GetCSWord(TString, 1, "-"))
tEnd = (GetCSWord(TString, 2, "-"))
Else
MsgBox "Problem with hyphenated zips"
End If

arrSize = Val(tEnd) - Val(tStart) + 1
ReDim tArray(arrSize)
tArray(1) = (tStart)
For j = (Val(tStart) - Val(tStart) + 2) To (Val(tEnd) - Val(tStart))
+ 1
tArray(j) = Format(Val(tArray(j - 1)) + 1, "000")
Next j
'Each zip is now loaded in the array
End If

Stop
End Function

The idea was to break down the zip ranges into each seperate zip, then pass
that back to a parent function to create a complete list of individual zips.
For example I would pass this on to the parent functiion "310, 312, 316-319,
398". So I would start with: AADC MACON GA 310 310, 312, 316-319, 398
End with: AADC MACON GA 310 310, 312, 316, 317, 318, 319, 398

Thanks
Mike

"Bob Phillips" wrote:

Mike,

Here is an example of what I think you are doing

Function ZipWhere(Zip)
Dim aryZips, aryAreas
Dim iPos As Long

ZipCodes aryZips, aryAreas
On Error Resume Next
iPos = Application.Match(Zip, aryZips, 0)
On Error GoTo 0
If iPos = 0 Then
ZipWhere = CVErr(xlErrNA)
Else
ZipWhere = aryAreas(iPos)
End If

End Function

Function ZipCodes(ByRef pZipCodes, ByRef pZipAreas)

'this is a simulation to load the array
pZipCodes = [{308,401,517}]
pZipAreas = [{"Atlanta GA","Austin TX","Birmingham AL"}]
'your code would be different here, but it
'would load the same arrays
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mikebres" wrote in message
...
First let me explain the big picture. I'm trying to make a function to
look
at list of zipcodes. They are listed in this format "Augusta GA" "298,
308-310". City name in one cell and the zips in another. I want the
function
to look at these entries and tell me if a certain zipcode is in the list.
For example if I enter a zip of 309 it should return Augusta GA.
Now, I'm at the point where I have made a function to fill the range into
an
array. The functions leaves me with an array of (308, 309, 310).
I have two questions. First of all is the best way to approach this?
Second if so, what do I need to know about passing arrays from one
function
to another.

Thanks
Mike