Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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, "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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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 "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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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 "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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert a range to a 2sequence of numbers?
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 "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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying numbers stored in a range??? | Excel Discussion (Misc queries) | |||
IF than statement in a range of numbers? please help? | Excel Discussion (Misc queries) | |||
Convert text numbers to numbers | Excel Worksheet Functions | |||
convert numbers to text | Excel Discussion (Misc queries) | |||
Auto convert an alphanumeric string (CIS9638S) to numbers only? | Excel Worksheet Functions |