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?