Hi John, I am trying to work with the formula solution you added (thank you,
thank you) but I don't think I understand how to use it. It's looking up to
Fed_X_Zones.xls right? But do I have to run the VBA on the excel sheet to get
a full listing of all the Zip Codes? Sorry to be so "challenged" with this
;-).

I did send you the file and hope you got it this time.

Christine


"Toppers" wrote:

Christine,
I haven't received the w/books but here are some changes:

First the macro, which should be put in "Fed_X_Zones". Assumes both sets of
data are on Sheet1 on each w/book

Sub ZipCodes()

Dim rng2 As Range
Dim wb1 As Workbooks
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long
Dim n1 As Long, n2 As Long, n As Long


Set ws1 = Worksheets("Sheet1")
Set ws2 = Workbooks("NORA1.xls").Worksheets("Sheet1")
Set rng2 = ws2.Cells(1, "A")
ws2.Columns("A:A").NumberFormat = "@"

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lastrow
If InStr(1, .Cells(r, "A"), "-") = 0 Then
n1 = Val(.Cells(r, "A"))
n2 = n1
Else
n1 = Val(Left(.Cells(r, "A"), InStr(1, .Cells(r, "A"), "-") - 1))
n2 = Val(Mid(.Cells(r, "A"), InStr(1, .Cells(r, "A"), "-") + 1,
255))
End If
For n = n1 To n2
rng2 = Format(n, "000xx")
rng2.Offset(0, 1) = .Cells(r, "B")
Set rng2 = rng2.Offset(1, 0)
Next n
Next r

End With

End Sub

And there is a formula solution:

=INDEX([Fed_X_Zones.xls]Sheet1!$B$1:$B$7,MATCH(LEFT(A1,3),LEFT([Fed_X_Zones.xls]Sheet1!$A$1:$A$7,3),1))

This has to be entered with Ctrl+ShifT+Enter when you see {} appear round
the formula.
This should be entered in the NORA1 in appropriate column.

It assumes Zip codes to be looked up are in column A of NORA1 and compares
the first three characters only.

Change range of Fed_X-Zones data as needed.

HTH

"Christine" wrote:

I'll send it right now. Thank you so much :-)

Christine


"Toppers" wrote:

Christine,
Is it possible to send me a copy of your workbooks?

Send to toppers<atnospam.johntopley.fsnet.co.uk

Remove "nospam"

FYI, the code below is looking for a "-" (minus sign) to determine the first
value in the range i.e to find 003 in 003-006. The next line of code (for n2)
finds 006.

n1 = Val(Left(.Cells(r, "A"), InStr(1, .Cells(r, "A"), "-") - 1))


And YES, once the codes have been listed, you can then do your VLOOKUP.

HTH

"Christine" wrote:

Hi Toppers,

Just a little more information for you that might help you...help me!

When i got the runtime error 9, I think it was because int he Workbook there
was only 1 Worksheet, Once I added a 2nd blank sheet, it gets past that line
in Debugging. Then I ran again and it gives me a runtime error 5 and stops at
the line:

n1 = Val(Left(.Cells(r, "A"), InStr(1, .Cells(r, "A"), "-") - 1))

but I don't know what that line is trying to do.

Thanks again,

Christine


"Toppers" wrote:

Try running this macro. If you are not familar with VBA code see the following:

http://www.cpearson.com/excel/codemods.htm


Use Alt + F11 to open the Visual Basic Editor (VBE)
Right click on VBAProject in the Project Window
Insert=Module
Copy and paste code below
Place cursor anywhere in code and press PF5 to run.

HTH


---------------------------------------------------------------------------------------------------
Sub ZipCodes()

Dim rng2 As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long
Dim n1 As Long, n2 As Long, n As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set rng2 = ws2.Cells(1, "A")
ws2.Columns("A:A").NumberFormat = "@"

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lastrow
n1 = Val(Left(.Cells(r, "A"), InStr(1, .Cells(r, "A"), "-") - 1))
n2 = Val(Mid(.Cells(r, "A"), InStr(1, .Cells(r, "A"), "-") + 1, 255))
For n = n1 To n2
rng2 = Format(n, "000xx")
rng2.Offset(0, 1) = .Cells(r, "B")
Set rng2 = rng2.Offset(1, 0)
Next n
Next r

End With

End Sub

"Christine" wrote:

I'm not even sure that the subject line makes sense. I tried to search for an
answer but I'm stummped as to what to search for! Hopefully someone here
(smarter than me) can help :-)

Here's the situation in an example form:

Spreadsheet 1 is set up with 5 Columns, headings = Company, Vendor#,
Contact, Phone, and Zip Code. I want to add another column called Zone and
populate the records using a lookup formula.

Spreadsheet 2 is set up with 2 Columns, Zip and Zone. The problem is Column
A which contains zip code, doesn't have the complete zip, it has a range that
says, zip codes starting with 000 thru zip codes starting with 003 are in
zone 5....for example:

Column A, Column B
000-003, 5
004-148, 6
149-159, 7

What I need is:

000xx, 5
001xx, 5
002xx, 5
003xx, 5
004xx, 6
005xx, 6
006xx, 6
etc. etc.

I tried text to columns and a find and replace but can't seem to get a full
listing.

Any ideas?