Convert a range to a 2sequence of numbers?

[Christine]

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?

 

[Toppers]

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]

Hi Toppers,

Thanks so much for your help. A couple of questions. These are actually 2
separate Workbooks, not worksheets so I'm assuming I need to modify the code
you wrote to reflect that but not sure what I need to do to tell it they are
separate books (maybe an exclamation mark?).

The spreadsheet with the Zones already is called Fed_X_Zones.xls and the one
I want to add the zone to is called Nora1.xls.

Secondly, I should run this from the Fed_X_Zones.xls spreadsheet....right?

Sorry I'm so bad with VBA, just recently started trying to use it. Powerful
stuff but very confusing

Thanks again for your help,

 

[Christine]

I get Runtime Error "9" Subscript out of range, when I try and run this as is.

Do I understand this correctly.....This code is meant to simply give me a
list of All zip codes in Column A on a new worksheet in the same spreadsheet
with the corresponding Zone in Column B on that new worksheet? If so, the
table I'm looking up to won't come into play until I do my Lookup?

Is that right?

Thanks again for your help,

Christine

 

[Christine]

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]

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

Send to toppers<at>nospam.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]

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

Christine

 

[Toppers]

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]

Hi John, Sorry about that. I didn't capture your entire email address in my
clipboard so the email was returned. I just sent it again!! In the meantime,
I'll try and see if I can make the code below work. Thanks so much!!!!

Christine

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

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

Christine

 

[Christine]

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

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

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

Christine

 

[Christine]

Hi John,

I tried to use the formula you sent me. I've read a bunch on using the
formula (at least I think so) but I don't think I understand where to enter
it as an array formula (that is what the CTRL+SHIFT+ENTER does right?).

I don't understand it yet....but I am learning something that I didn't know
before. Thanks!

Christine

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

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

Christine